Home » Developer & Programmer » Application Express, ORDS & MOD_PLSQL » number error in apex during uploading csv file to table
number error in apex during uploading csv file to table [message #459918] Wed, 09 June 2010 00:44 Go to next message
shobhakashyapgmailcom
Messages: 58
Registered: June 2010
Location: India
Member

hi

i need to upload the csv file to table.i got the code for it in OTN
site.but it is working well only for the varchar2 type,not with the number type.if i mension number type its giving error during uploading,ERROR:character to Number..

one thing i found in my cade is..i ve a data of type number with single value.but it gives error if i mention number type so i declared it as varchar2(1)..but it is giving ORA-12899: value too large for column "DEV"."SRS_COMC_DATA"."PRIORITY" (actual: 3, maximum: 1).if i declare it as Varchar2(3)..it works fine.but it creates some problem further..plz help me to resolve it soon..
urgent...

[MERGED by LF]

[Updated on: Thu, 10 June 2010 04:02] by Moderator

Report message to a moderator

Re: number error in apex during uploading csv file to table [message #459922 is a reply to message #459918] Wed, 09 June 2010 00:52 Go to previous messageGo to next message
Michel Cadot
Messages: 67283
Registered: March 2007
Location: Nanterre, France, http://...
Senior Member
Account Moderator
Create it with "VARCHAR2(1 CHAR)", default is "1 BYTE" and I bet you have an UTF8-like character set which requires more than 1 byte for non old ascii character.

Regards
Michel
Re: number error in apex during uploading csv file to table [message #459934 is a reply to message #459922] Wed, 09 June 2010 01:49 Go to previous messageGo to next message
shobhakashyapgmailcom
Messages: 58
Registered: June 2010
Location: India
Member

hi,
thanks for your reply..
am not understanding wats the changes in need to made in my code..
i have mensioned VARCHAR2(1 CHAR) only in my table..

my code is as follows to upload the csv to table using oracle apex application
DECLARE
   v_clob_data      CLOB;
   v_blob_data      BLOB;
   v_clob_len       NUMBER;
   v_position       NUMBER;
   --v_raw_chunk       RAW(10000);
   v_char           CHAR (1);
   c_chunk_len      NUMBER := 1;
   v_line           VARCHAR2 (32767) := NULL;
   v_data_array     wwv_flow_global.vc_arr2;
   v_rows           NUMBER;
   v_count          NUMBER := 0;
   v_add            NUMBER := 1;
   v_file_size      INTEGER := DBMS_LOB.lobmaxsize;
   v_dest_offset    INTEGER := 1;
   v_src_offset     INTEGER := 1;
   v_blob_csid      NUMBER := DBMS_LOB.default_csid;
   v_lang_context   NUMBER := DBMS_LOB.default_lang_ctx;
   v_warning        INTEGER;
   sep              VARCHAR2 (1) := '~';
BEGIN
   DELETE FROM BO_TEST_CSV;

   DBMS_LOB.createtemporary (v_clob_data, TRUE);

   -- Read data from wwv_flow_files
   SELECT blob_content
     INTO v_blob_data
     FROM wwv_flow_files
    WHERE last_updated = (SELECT MAX (last_updated) FROM wwv_flow_files)
          AND id = (SELECT MAX (id) FROM wwv_flow_files);

   DBMS_LOB.CONVERTTOCLOB (v_clob_data,
                           v_blob_data,
                           v_file_size,
                           v_dest_offset,
                           v_src_offset,
                           v_blob_csid,
                           v_lang_context,
                           v_warning);

   v_clob_len := DBMS_LOB.getlength (v_clob_data);
   v_position := 1;

   -- Read and convert binary to char
   WHILE (v_position <= v_clob_len)
   LOOP
      -- v_raw_chunk := dbms_lob.substr(v_clob_data,c_chunk_len,v_position);
      v_char := DBMS_LOB.SUBSTR (v_clob_data, c_chunk_len, v_position);
      -- v_char := chr(hex_to_decimal(rawtohex(v_raw_chunk)));
      v_line := v_line || v_char;
      v_position := v_position + c_chunk_len;

      IF v_char = CHR (10)
      THEN
         -- Convert comma to : to use wwv_flow_utilities
         -- v_line := REPLACE (v_line, ',', ':');
         -- Convert each column separated by : into array of data
         -- v_data_array := wwv_flow_utilities.string_to_table (v_line);
         v_line := REPLACE (v_line, ',', '~');
         v_data_array := wwv_flow_utilities.string_to_table (v_line, sep);

         IF v_count > 0
         THEN
            EXECUTE IMMEDIATE 'insert into SRS_COMC_DATA(OUTAGE,TAR_#,CUSTOMER_NAME,SEVERITY,PRIORITY,RECEIVED,RESOLUTION_TIME)
 values (:1,:2,:3,:4,:5,:6,:7)'
               USING v_data_array (1),
                     v_data_array (2),
                     v_data_array (3),
                     v_data_array (6),
                     v_data_array (7),
                     v_data_array (4),
                     v_data_array (5);
         END IF;

         -- Clear out
         v_count := v_count + v_add;
         v_line := NULL;
      END IF;
   END LOOP;
END;


table contains::
OUTAGE          VARCHAR2(4000)	         
TAR_#           VARCHAR2(4000)	  
CUSTOMER_NAME   VARCHAR2(4000)	
SEVERITY,       VARCHAR2(1)
PRIORITY,       VARCHAR2(1)
RECEIVED,       VARCHAR2(4000)
RESOLUTION_TIME VARCHAR2(4000)


i need to make the sevirity and priority column as NUMBER TYPE

please help out

[EDITED by LF: reformatted code and applied [code] tags]

[Updated on: Wed, 09 June 2010 01:54] by Moderator

Report message to a moderator

Re: number error in apex during uploading csv file to table [message #459937 is a reply to message #459934] Wed, 09 June 2010 02:00 Go to previous messageGo to next message
Littlefoot
Messages: 21578
Registered: June 2005
Location: Croatia, Europe
Senior Member
Account Moderator
Your first statement was:
Quote:
i need to upload the csv file to table

  • How does the CSV file look like?
  • Does the target table exist? Is it empty? If so, it is (I guess) easy to modify SEVERITY and PRIORITY columns' datatypes from VARCHAR2 to NUMBER.
  • Your last message contains hell of a code; that's not simple at all. Perhaps that job could be done in a simpler manner (such as SQL*Loader or external tables feature)?
Re: number error in apex during uploading csv file to table [message #459942 is a reply to message #459937] Wed, 09 June 2010 02:18 Go to previous messageGo to next message
shobhakashyapgmailcom
Messages: 58
Registered: June 2010
Location: India
Member

hi,
am using oracle application express.
yes my table is empty.
i know if my table is empty i can change it to number.and i tried it also,but during uploading am getting an error like ORA-12899: value too large for column "DEV"."SRS_COMC_DATA"."PRIORITY" (actual: 3, maximum: 1).
if i mension it as number i get charcter to number conversion error.

am not able to find out where am facing the problem.


Re: number error in apex during uploading csv file to table [message #459949 is a reply to message #459942] Wed, 09 June 2010 02:37 Go to previous messageGo to next message
Littlefoot
Messages: 21578
Registered: June 2005
Location: Croatia, Europe
Senior Member
Account Moderator
What about the rest of my questions?
Re: number error in apex during uploading csv file to table [message #459951 is a reply to message #459949] Wed, 09 June 2010 02:44 Go to previous messageGo to next message
shobhakashyapgmailcom
Messages: 58
Registered: June 2010
Location: India
Member

1.csv file is attached with this mail please check out..
2.target table is created already.
3.i dnt kw in which manner we can load the data to apex DB table from .CSV FILE
  • Attachment: CHECK.CSV
    (Size: 5.79KB, Downloaded 463 times)

[Updated on: Wed, 09 June 2010 02:46]

Report message to a moderator

Re: number error in apex during uploading csv file to table [message #459980 is a reply to message #459951] Wed, 09 June 2010 05:02 Go to previous messageGo to next message
Littlefoot
Messages: 21578
Registered: June 2005
Location: Croatia, Europe
Senior Member
Account Moderator
This is a simple CSV file. Regarding information you provided, I created a table as well as sample control file (used by SQL*Loader) and loaded some records into a table.

This is the first (slightly modified) CSV line and columns you mentioned earlier - you should tell us what goes where (these are just my assumptions)
811953,  7634826.993,  3D SYSTEMS,    7/30/2009 0:00,   5:07:00,          1,         1
------   -----------   -------------  ---------------   ---------------   --------   --------
outage   tar_#         customer_name  received          resolution_time   severity   priority

OUTAGE          VARCHAR2(4000)
TAR_#           VARCHAR2(4000)	  
CUSTOMER_NAME   VARCHAR2(4000)	
SEVERITY,       VARCHAR2(1)      -> changed to NUMBER(1), as you wanted it to
PRIORITY,       VARCHAR2(1)      -> changed to NUMBER(1), as you wanted it to
RECEIVED,       VARCHAR2(4000)
RESOLUTION_TIME VARCHAR2(4000)

However, why do you want to put (an obvious) date value (received and resolution_time) into a character column?

Here's how it goes:
SQL> desc apex_test
 Name                                      Null?    Type
 ----------------------------------------- -------- ----------------------------
 OUTAGE                                             VARCHAR2(4000)
 TAR_#                                              VARCHAR2(4000)
 CUSTOMER_NAME                                      VARCHAR2(4000)
 SEVERITY                                           NUMBER(1)
 PRIORITY                                           NUMBER(1)
 RECEIVED                                           VARCHAR2(4000)
 RESOLUTION_TIME                                    VARCHAR2(4000)

SQL>

A control file:
load data
infile check.csv
replace
into table apex_test
fields terminated by ','
trailing nullcols
  (outage,
   tar_#,
   customer_name,
   received,
   resolution_time,
   severity,
   priority
)

Loading session and a few loaded records:
SQL> $sqlldr scott/tiger@ora10 control=test8.ctl log=test8.log

SQL*Loader: Release 10.2.0.1.0 - Production on Sri Lip 9 11:53:29 2010

Copyright (c) 1982, 2005, Oracle.  All rights reserved.

Commit point reached - logical record count 64
Commit point reached - logical record count 98

SQL> select count(*) from apex_test;

  COUNT(*)
----------
        98

SQL> select * from apex_test where rownum < 6;

OUTAGE     TAR_#            CUSTOMER_NAME          SEVERITY   PRIORITY RECEIVED        RESOLUTION
---------- ---------------- -------------------- ---------- ---------- --------------- ----------
811953     7634826.993      3D SYSTEMS                    1          1 7/30/2009 0:00  5:07:00
791540     7498330.993      3D SYSTEMS                    1          2 5/10/2009 0:00  1:47:00
797537     21347888.6       A3I                           1          2 6/7/2009 0:00   1:30:00
796302     21321260.6       AAFES                         1          1 5/31/2009 0:00  1:00:00
791043     21212668.6       ACHIEVO CORP                  1          1 5/8/2009 0:00   0:28:00

SQL>


I don't know ... something like this seems to be far simpler than your code.

However: I don't know how to make SQL*Loader work from an Apex application. SQLLDR is an executable and it runs at operating system command prompt. I guess that there is a way to call an executable from Apex.
Its "advantage" is that you can run it on any computer with installed Oracle Client software (at least its Utilities). Disadvantage is the same - you have to install SQL*Loader.

External table works on the same principle, but has its (dis)advantages too: user has to have access to the database server (because CSV file must be on a server); directory read/write privileges must be granted. User doesn't have to install any Oracle software on his/her PC.

If you can't afford anything I mentioned, maybe you'll have to fix program you posted earlier.

On the other hand, there might be some other options, but none crosses my mind at the moment.
character to number conversion error [message #460207 is a reply to message #459918] Thu, 10 June 2010 03:27 Go to previous messageGo to next message
shobhakashyapgmailcom
Messages: 58
Registered: June 2010
Location: India
Member

hi
am trying to upload csv file to oracle database using apex when i select the file using file browser and click on the button.when i upload the value of type number it gives the error:ORA-06502: PL/SQL: numeric or value error: character to number conversion error

please give me the solution for this

i need to write a pl/sql process in apex.
can u tell me what exactly i shoud mension for uploading
my table looks like

coloumn type

col1 number(2)
col2 number(2)
col3 number(2)
col4 number(2)

please tell me the steps i need to follow

urgent requirement
Re: character to number conversion error [message #460216 is a reply to message #460207] Thu, 10 June 2010 04:02 Go to previous messageGo to next message
Littlefoot
Messages: 21578
Registered: June 2005
Location: Croatia, Europe
Senior Member
Account Moderator
Quote:
character to number conversion error

Don't do that the way you are doing it now. You can't convert 'A' into a number, for example. Either input data is wrong, or the way you manipulate it. As you refuse to provide useful information, I hope that "urgent" in your part of the world is measured in weeks (or more) rather than hours (or less).
Re: character to number conversion error [message #460227 is a reply to message #460216] Thu, 10 June 2010 04:29 Go to previous messageGo to next message
shobhakashyapgmailcom
Messages: 58
Registered: June 2010
Location: India
Member

can u tell me how can i implement in my application with code if possible..

its very urgent in tems of hrs itself..not in terms of week or more .so please help me to resolve my issue..

Re: character to number conversion error [message #460228 is a reply to message #460227] Thu, 10 June 2010 04:37 Go to previous messageGo to next message
Littlefoot
Messages: 21578
Registered: June 2005
Location: Croatia, Europe
Senior Member
Account Moderator
There's nothing I'd like to add to my previous message (#459980).
Re: character to number conversion error [message #460232 is a reply to message #460228] Thu, 10 June 2010 04:53 Go to previous messageGo to next message
shobhakashyapgmailcom
Messages: 58
Registered: June 2010
Location: India
Member

sorry to disturb u again..

my data is not alphabetic value..
my data value contains 0-5 number of length 1
Re: character to number conversion error [message #460246 is a reply to message #460232] Thu, 10 June 2010 05:41 Go to previous messageGo to next message
Littlefoot
Messages: 21578
Registered: June 2005
Location: Croatia, Europe
Senior Member
Account Moderator
If you say so.
Oracle disagrees with you.

Perhaps you should try again (regarding the whole job): what is the input? Provide sample data (is it the same as posted in the CSV file)? How do you "load" these values into the table? Is it still your procedure (posted in a message ##459934), or did you implement something I suggested earlier (SQL*Loader)? What causes "ORA-06502: PL/SQL: numeric or value error: character to number conversion error"?

Because, I have no idea where we are at the moment.
Re: character to number conversion error [message #460385 is a reply to message #460246] Thu, 10 June 2010 23:38 Go to previous messageGo to next message
shobhakashyapgmailcom
Messages: 58
Registered: June 2010
Location: India
Member

am using all single digit number value..
yes am using my procedure to upload.

col1 number(2)
col2 number(2)
col3 number(2)
col4 number(2)

above table i use and i tried to insert data from csv to DB is
1 2 3 4
1 2 3 4
1 3 2 4

am not able to implement SQL Loader in my Apex Application
Re: character to number conversion error [message #461257 is a reply to message #460246] Thu, 17 June 2010 05:07 Go to previous messageGo to next message
shobhakashyapgmailcom
Messages: 58
Registered: June 2010
Location: India
Member

thank u littlefoot..i have done with the uploading part of the data with your ideas

thanks for assistance
Re: character to number conversion error [message #461317 is a reply to message #461257] Thu, 17 June 2010 06:37 Go to previous message
Littlefoot
Messages: 21578
Registered: June 2005
Location: Croatia, Europe
Senior Member
Account Moderator
Nice.

Perhaps you could describe what you exactly did, how (which code you used) so that someone, who has the same problem, finds this discussion and follows your steps.
Previous Topic: How can i use LOV as tabs
Next Topic: duplicat alias in query, but the same time none duplicats..
Goto Forum:
  


Current Time: Mon Aug 03 10:19:30 CDT 2020