One of the web development projects using Oracle APEX I’m working on is to upload the content of a text file in csv format into database. By default, all files using the “File Browse..” element will be uploaded to the “wwv_flow_files” table which has structure as follows:
SQL> desc wwv_flow_files Name Type Nullable Default Comments ------------ -------------- -------- ------- -------- ID NUMBER FLOW_ID NUMBER NAME VARCHAR2(90) FILENAME VARCHAR2(400) Y TITLE VARCHAR2(255) Y MIME_TYPE VARCHAR2(48) Y DOC_SIZE NUMBER Y DAD_CHARSET VARCHAR2(128) Y CREATED_BY VARCHAR2(255) Y CREATED_ON DATE Y UPDATED_BY VARCHAR2(255) Y UPDATED_ON DATE Y LAST_UPDATED DATE Y CONTENT_TYPE VARCHAR2(128) Y BLOB_CONTENT BLOB Y LANGUAGE VARCHAR2(30) Y DESCRIPTION VARCHAR2(4000) Y FILE_TYPE VARCHAR2(255) Y FILE_CHARSET VARCHAR2(128) Y
Even though the text file contains character data, the uploaded file content will be stored in the “binary format” in a BLOB column named BLOB_CONTENT. Due to nature of BLOB, to read character out, the conversion using “chr” function is required. Please see a good article about this concept and conversion by Natalka Roshak on the orafaq at http://www.orafaq.com/node/895.
I modified the sample script provided in above article to suite my need – reading each line of the text file and insert into a target table.
DECLARE
v_blob_data BLOB;
v_blob_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;
BEGIN
-- Read data from wwv_flow_files
select blob_content into v_blob_data
from wwv_flow_files where name = 'F29800/Data_CSV.csv';
v_blob_len := dbms_lob.getlength(v_blob_data);
v_position := 1;
-- Read and convert binary to char
WHILE ( v_position <= v_blob_len ) LOOP
v_raw_chunk := dbms_lob.substr(v_blob_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;
-- When a whole line is retrieved
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);
-- Insert data into target table
EXECUTE IMMEDIATE 'insert into TABLE_X (v1, v2, v3, v4)
values (:1,:2,:3,:4)'
USING
v_data_array(1),
v_data_array(2),
v_data_array(3),
v_data_array(4);
-- Clear out
v_line := NULL;
END IF;
END LOOP;
END;
/
May 11th, 2008 6:46 pm
Hi Ittichai,
Awesome post !! this really helped me for my similar project.
Thanks a lot again !!
Regards,
Advait Deo
May 11th, 2008 10:09 pm
Good to hear that. Thanks for stopping by. IC
July 11th, 2008 11:04 am
Its good but I am facing one issue. In my csv files one of the column is having ,(comma) bcoz of which above code is considering one column text as two coulmns.
Like one coulmn Name is having value: “Advait,Deo” but codce is treating it as two columns “Advait” and “Deo”.
Any workaround for this.
Thanks.
July 12th, 2008 1:59 am
Question is that is it one-time thing?
If it is, after the below line, which converts every “,” into “:” for wwv utility,
– Convert comma to : to use wwv_flow_utilities
v_line := REPLACE (v_line, ‘,’, ‘:’);
You could convert Advait:Deo back into Advait,Deo using the same replace command.
v_line := REPLACE (v_line, ‘Advait:Deo’, ‘Advait,Deo’);
Otherwise, you might need to use different delimiter (e,g, ~) instead of “,”.
The replace would be like this instead
v_line := REPLACE (v_line, ‘~’, ‘:’);
Let me know whether or not it works.
IC
August 15th, 2008 6:07 pm
Hi
I am trying to use the code here.I am getting following error.
ORA-12899: value too large for column "TS_APEX"."TABLE_X"."V1" (actual: 913, maximum: 400)
Error
and the debug looks like >>
Branch point: BEFORE_PROCESSING
0.30: Processing point: AFTER_SUBMIT
0.30: …Process "Data_to_table": PLSQL (AFTER_SUBMIT) DECLARE v_blob_data BLOB; v_blob_len NUMBER; v_position NUMBER; v_raw_chunk RAW(10000); v_char CHAR(1); c_chunk_len number := 1; v_line VARCHAR2 (32767) := NUL
5.16: Encountered unhandled exception in process type PLSQL
5.16: Show ERROR page…
5.16: Performing rollback…
Thanks
Can you please describe table_x fields. I suspect there might be a problem. Please suggest
August 16th, 2008 2:07 am
Yes that is likely the issue. Actually it does not matter what I have. The column length of target table should be big enough based on your data.
Looking from your posted error, I assume that your V1 column has only 400 characters in length, but data is 913 chars.
ORA-12899: value too large for column “TS_APEX”.”TABLE_X”.”V1″ (actual: 913, maximum: 400)
I guess if you try to make it bigger, it should be okay. Let me know how it goes.
August 17th, 2008 11:43 pm
Hi Ittichai
I could figure out the error. The file wass not saved as .csv file, it was saved as .xls file which was causing problem. If my first row of data contains column names, i would like to avoid them while inserting into table. Is there any way to avoid first row while inserting into table?
Thanks for your time.
Sandeep
August 18th, 2008 2:04 am
Glad to hear that you figured the issue out.
Since the script is reading line by line, you can simply add logic (e.g., line_count) to ignore the first line.
September 16th, 2008 11:42 pm
Hi. I’m trying to use this, but when I reach the point to save it to the existing table, it tells me ORA-00947: not enough values.
September 16th, 2008 11:51 pm
Bonnie,
This has to do with the number of values for columns must be the same as number of values in USING.
Sample below has 4 values -
EXECUTE IMMEDIATE ‘insert into TABLE_X (v1, v2, v3,v4)
values (:1,:2,:3,:4)’
USING
v_data_array(1),
v_data_array(2),
v_data_array(3),
v_data_array(4);
September 29th, 2008 10:16 pm
Sorry. I’ve been on vacation. This is my code:
EXECUTE IMMEDIATE ‘insert into UPLOAD_TABLE(application, extension, phone)
VALUES (:1,:2,:3)’
USING v_data_array(1),
v_data_array(2),
v_data_array(3);
I am still getting “not enough values”
October 1st, 2008 3:28 am
Bonnie,
That is interesting. The ORA-947- not enough values is the insert issue. Will you be able to do it in sqlplus? What is table definition?
October 10th, 2008 1:29 am
Great Article! Works like a treat!
One question though.. How would you go about reporting back to the user the success of the upload? What i am looking for specifically is reporting back how many records were inserted, how many failed, etc. Just like the standard data upload mechanism..
Any ideas?
October 10th, 2008 3:27 am
David,
I would think the easiest way is to catch exception error. You can simply wrap the EXECUTE IMMEDIATE of insert with BEGIN/END and add EXCEPTION.
BEGIN
:
:
BEGIN
EXECUTE IMMEDIATE ‘insert into TABLE_X (v1, v2, v3, v4)
values (:1,:2,:3,:4)’
USING
v_data_array(1),
v_data_array(2),
v_data_array(3),
v_data_array(4);
EXCEPTION
WHEN OTHERS THEN
[[Handle a failed record - you could put the failed data into a temp table]]
END;
:
:
END;
IC
January 21st, 2009 10:20 am
To answer “anonymous” who asked this question -
“I am using oracle apex 3.1 connected to oracle, i have a table named adjbatch, i want this information to be uploaded to a CSV file and saved in the local directly. How do i do this in APEX.”
You can use the standard default “export to csv” feature as a part of report, or please read Scott Spendolini’s post who wrote “how to do custom export to csv” at http://spendolini.blogspot.com/2006/04/custom-export-to-csv.html.
April 11th, 2009 8:02 am
Hi,
Super post, Need to mark it on Digg
Thank you
GlenStef
April 23rd, 2009 8:42 am
I am not able to query wwv_flow_files using a method other than within APEX. I am using PL/SQL Developer.
Is there an issue with privleges? I typically find it easier to run queries using something other than APEX.
Any thoughts or experience on this?
April 23rd, 2009 10:25 am
David,
Querying these APEX objects outside APEX will require to set workspace ID before. I ran below from PL/SQL Developer’s command line.
First you will need to identify the workspace ID.
SQL> col WORKSPACE_ID format 999999999999999
SQL> select workspace, schemas, workspace_id from apex_workspaces;
WORKSPACE SCHEMAS WORKSPACE_ID
---------- ---------- -------------------
EDW 1 858728211457679
You won’t be able to see anything yet.
SQL> select id, flow_id, name, filename from wwv_flow_files;
ID FLOW_ID NAME FILENAME
---------- ---------- ------------------------------ --------------------
But after setting the workspace ID, you should be able to see its contents.
SQL> exec wwv_flow_api.set_security_group_id('858728211457679');
PL/SQL procedure successfully completed
SQL> select id, flow_id, name, filename from wwv_flow_files;
ID FLOW_ID NAME FILENAME
---------- ---------- ------------------------------ --------------------
1.64130293 488 F31299/matrix_order_installer_ matrix_order_install
1.0.sql er_1.0.sql
June 17th, 2009 2:30 am
hi ,
i got this error while uploading the excel sheet ORA-01403: no data found .. can u help me in solve this error
June 17th, 2009 8:36 am
Ram,
The best approach to find the root cause is to use the PL/SQL tools (like Toad or PL/SQL developer) to debug codes line-by-line.
If you give me more information of your issue, I can try to help out. I also recommend you to take advantage of Oracle’s forum.
Ittichai
June 17th, 2009 9:06 am
hi,
thanxs for replying i have solved this issue by debugging line by line … thanks a lot for posting this ….
July 3rd, 2009 1:27 am
Hi
This helps a lot with csv files, but what do you do when the upload file is a fixed length ASCII file with a carriage return as the end of a line?
July 3rd, 2009 8:38 am
You can just simply replace lines with comma conversion and wwv_flow_utilities.string_to_table function with a loop to parse (substr) through your fixed length data.
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);
for i in 1 .. v_num_of_cols loop
v_data_array(i) := substr(v_line, ((i-1)*v_col_length)+1, v_col_length);
end loop;
:
:
Note that in the declaration section, you will have to create two numeric variables v_num_of_cols and v_col_length for the number of columns of each row and its fixed length respectively. I tested with a small set of data, it seems to work fine.
Hope this helps.
Ittichai
July 5th, 2009 6:52 am
Hi
Yes it helped, thank you very much. It worked perfectly until I got a file where the column lenghts is not the same size – this is the file :
SPPTY001 ASHAI DN SHAI DN 0000000123456789 051001TSALARIS / SALARY 010000028954600000000000999999999990000000000000000000000200905070000000001
SPPTY003 AKENNEDY B KENNEDY B 0000000123456789 632005SSALARIS / SALARY 010000034875700000000000999999999990000000000000000000000200905070000000001
column lenghts is 16,1,30,30,26,6,1,20,2,11,44,8,10.
I am very new to apex and oracle, please could you assist me.
July 5th, 2009 10:22 am
You may just need to create two new variables – one numeric for starting pointer (starting with 1), other as numeric array containing column lengths.
For every loop you just keep the pointer moving to the beginning of the next column.
for i in 1 .. v_num_of_cols loop
v_data_array(i) := substr(v_line, v_start, v_col_length(i));
v_start := v_start + v_col_length(i);
end loop;
July 6th, 2009 4:56 am
Thank you very much for all your help – this is brilliant!
July 14th, 2009 4:48 am
Hi Ittichai
I have tried your solution but I have run into a problem. I get ORA-00928: missing SELECT keyword error when running the script.
Here is copy of my code.
DECLARE
v_blob_data BLOB;
v_blob_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;
BEGIN
57_UPLOAD;
— Read data from wwv_flow_files
select blob_content
into v_blob_data
from wwv_flow_files
where name =
v_blob_len := dbms_lob.getlength(v_blob_data);
v_position := 1;
— Read and convert binary to char
WHILE ( v_position <= v_blob_len ) LOOP
v_raw_chunk := dbms_lob.substr(v_blob_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;
— When a whole line is retrieved
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);
— Insert data into target table
EXECUTE IMMEDIATE ‘insert into DATA_UPLD(DATE, AMOUNT, BALANCE, DESCRIPTION)
values (:1,:2,:3,:4)’
USING
to_date(v_data_array(1),’yy/mm/dd’),
to_number(v_data_array(2)),
to_number(v_data_array(3)),
v_data_array(4);
— Clear out
v_line := NULL;
END IF;
END LOOP;
END;
Any assistance to this newbie will be greatly appreciated.
Regards
Jacques
July 14th, 2009 10:43 am
Jacques,
I tried your codes on my apex.oracle.com (http://apex.oracle.com/pls/otn/f?p=51832:15), and did not see any issue. The only problem is the table column DATE which is not allowed, so I created it with UPLD_DATE instead.
The csv file has data as follows:
09/07/01,2,3,ABC
09/01/08,3,1,DEF
Every time, the Upload button is clicked, the above process will be executed, so you will see two rows being inserted for every click.
Please check your table’s column name and/or let me know which line the error is on?
October 6th, 2009 8:08 am
Hi Ittichai,
I have tried your solution and it works very well.
Great job!
In my teamwork we have built the upload procedure for CSV files on Apex.
During the upload phase we have notice that some files have some problems.
The reason is that we have some strings utf8 encoding (for example some Swedish customers with special characters).
We have tried to solve this problem but unfortunately we couldn’t.
This is our code:
declare
v_blob_data blob;
v_blob_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_yes_no varchar2(1) := ‘Y’;
v_user varchar2(255) := ‘ADMIN’;
v_load_date date := trunc(sysdate);
v_rec_no number := 1;
begin
XXDESCOR.PR_XXDESCOR_TRUNC_UPL_SALESINC();
commit;
XXDESCOR.PR_XXDESCOR_ANALYZE();
– read data from wwv_flow_files
15_UPLOAD_CSV;
select blob_content into v_blob_data
from wwv_flow_files where name =
v_blob_len := dbms_lob.getlength(v_blob_data);
v_position := 1;
– read and convert binary to char
while ( v_position <= v_blob_len ) loop
v_raw_chunk := dbms_lob.substr(v_blob_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;
– when a whole line is retrieved
if v_char = chr(10) then
– convert comma to : to use wwv_flow_utilities
v_line := replace (v_line, ';', ':');
v_line := replace (v_line, chr(13), '');
v_line := replace (v_line, chr(10), '');
– convert each column separated by : into array of data
v_data_array := wwv_flow_utilities.string_to_table (v_line);
– insert data into target table
execute immediate 'insert into DE_SCOR_UPL_SALES_INC (SCORECARD_ID, SCENARIO, SCORECARD_VIEW, ENTITY_OF_INVOICE
, SALES_CHANNEL, CUSTOMER, COUNTRY, APPLICATION, PRODUCT_FAMILY
, UNITS, NET_TURNOVER, TOTAL_COGS, VCE, G_AND_A, CENTRAL_R_AND_D
, SALES_AND_MARKETING, CENTRAL_SALES_AND_MARKETING, OTHER_INC_EXP
, BAD_DEBT_PROVISION, OTHER_VARIANCES, PROVISION, IP_SFC_VARIANCE
, OTHER_NON_OPERATING, DES_YEAR, DES_MONTH, IS_ACTIVE, CREATED_BY
, CREATION_DATE, LAST_UPDATE_BY, LAST_UPDATE_DATE)
values (:1, :2, :3, :4, :5, :6, :7, :8, :9, :10, :11, :12, :13, :14, :15, :16, :17, :18, :19, :20
, :21, :22, :23, :24, :25, :26, :27, :28, :29, :30)'
using
v_rec_no,
trim(v_data_array(1)),
trim(v_data_array(2)),
trim(v_data_array(3)),
trim(v_data_array(4)),
trim(v_data_array(5)),
trim(v_data_array(6)),
trim(v_data_array(7)),
trim(v_data_array(8)),
to_number(trim(v_data_array(9))),
to_number(trim(v_data_array(10))),
to_number(trim(v_data_array(11))),
to_number(trim(v_data_array(12))),
to_number(trim(v_data_array(13))),
to_number(trim(v_data_array(14))),
to_number(trim(v_data_array(15))),
to_number(trim(v_data_array(16))),
to_number(trim(v_data_array(17))),
to_number(trim(v_data_array(18))),
to_number(trim(v_data_array(19))),
to_number(trim(v_data_array(20))),
to_number(trim(v_data_array(21))),
to_number(trim(v_data_array(22))),
trim(v_data_array(23)),
trim(v_data_array(24)),
v_yes_no,
v_user,
v_load_date,
v_user,
v_load_date;
– clear out
v_line := null;
v_rec_no := v_rec_no + 1;
end if;
end loop;
end;
Could you help us?
Any assistance will be greatly appreciated.
October 6th, 2009 8:09 am
Hi Ittichai,
I have tried your solution and it works very well.
Great job!
In my teamwork we have built the upload procedure for CSV files on Apex.
During the upload phase we have notice that some files have some problems.
The reason is that we have some strings utf8 encoding of some Swedish customers with special characters.
We have tried to solve this problem but unfortunately we couldn’t.
This is our code:
declare
v_blob_data blob;
v_blob_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_yes_no varchar2(1) := ‘Y’;
v_user varchar2(255) := ‘ADMIN’;
v_load_date date := trunc(sysdate);
v_rec_no number := 1;
begin
XXDESCOR.PR_XXDESCOR_TRUNC_UPL_SALESINC();
commit;
XXDESCOR.PR_XXDESCOR_ANALYZE();
– read data from wwv_flow_files
15_UPLOAD_CSV;
select blob_content into v_blob_data
from wwv_flow_files where name =
v_blob_len := dbms_lob.getlength(v_blob_data);
v_position := 1;
– read and convert binary to char
while ( v_position <= v_blob_len ) loop
v_raw_chunk := dbms_lob.substr(v_blob_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;
– when a whole line is retrieved
if v_char = chr(10) then
– convert comma to : to use wwv_flow_utilities
v_line := replace (v_line, ';', ':');
v_line := replace (v_line, chr(13), '');
v_line := replace (v_line, chr(10), '');
– convert each column separated by : into array of data
v_data_array := wwv_flow_utilities.string_to_table (v_line);
– insert data into target table
execute immediate 'insert into DE_SCOR_UPL_SALES_INC (SCORECARD_ID, SCENARIO, SCORECARD_VIEW, ENTITY_OF_INVOICE
, SALES_CHANNEL, CUSTOMER, COUNTRY, APPLICATION, PRODUCT_FAMILY
, UNITS, NET_TURNOVER, TOTAL_COGS, VCE, G_AND_A, CENTRAL_R_AND_D
, SALES_AND_MARKETING, CENTRAL_SALES_AND_MARKETING, OTHER_INC_EXP
, BAD_DEBT_PROVISION, OTHER_VARIANCES, PROVISION, IP_SFC_VARIANCE
, OTHER_NON_OPERATING, DES_YEAR, DES_MONTH, IS_ACTIVE, CREATED_BY
, CREATION_DATE, LAST_UPDATE_BY, LAST_UPDATE_DATE)
values (:1, :2, :3, :4, :5, :6, :7, :8, :9, :10, :11, :12, :13, :14, :15, :16, :17, :18, :19, :20
, :21, :22, :23, :24, :25, :26, :27, :28, :29, :30)'
using
v_rec_no,
trim(v_data_array(1)),
trim(v_data_array(2)),
trim(v_data_array(3)),
trim(v_data_array(4)),
trim(v_data_array(5)),
trim(v_data_array(6)),
trim(v_data_array(7)),
trim(v_data_array(8)),
to_number(trim(v_data_array(9))),
to_number(trim(v_data_array(10))),
to_number(trim(v_data_array(11))),
to_number(trim(v_data_array(12))),
to_number(trim(v_data_array(13))),
to_number(trim(v_data_array(14))),
to_number(trim(v_data_array(15))),
to_number(trim(v_data_array(16))),
to_number(trim(v_data_array(17))),
to_number(trim(v_data_array(18))),
to_number(trim(v_data_array(19))),
to_number(trim(v_data_array(20))),
to_number(trim(v_data_array(21))),
to_number(trim(v_data_array(22))),
trim(v_data_array(23)),
trim(v_data_array(24)),
v_yes_no,
v_user,
v_load_date,
v_user,
v_load_date;
– clear out
v_line := null;
v_rec_no := v_rec_no + 1;
end if;
end loop;
end;
Could you help us?
Any assistance will be greatly appreciated.
October 7th, 2009 7:45 am
Giorgio,
What exact error message did you receive? Can you please send me your sample data?
October 7th, 2009 9:26 am
Thank you for your quick answer.
The question is that we don’t have any errors, because the import procedure completes successfully.
At the end of that procedure we have a check that matches the csv file with the imported data, in this phase we have an error that shows a problems with a Swedish customer name.
One of our customers has this name:
Båt & Maskin
but when we put it on db and we check in Apex on web application ( so in not a problem with the codificatio, because on web we have the same transcoding)we have that record:
B t & Maskin
So the matter is on the codification of the field during the upload not on the upload of the file,in fact the other rows are perfect.
If you have any further question I’m glade to explain better my problem.
Regards
October 20th, 2009 9:17 am
Hi Ittichai,
I post you an example of the code we are using and a sample csv of our customers.
If you can help us with your precious job we’ll appreciate very much.
This is a sample of the customers csv:
Ferrari;Italia
Båt & Maskin;Svezia
Jörgen Lushgy;Norvegia
Gonzales;Spagna
The upload function used for import is:
declare
v_blob_data blob;
v_blob_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_yes_no varchar2(1) := ‘Y’;
v_user varchar2(255) := ‘ADMIN’;
v_load_date date := trunc(sysdate);
v_rec_no number := 1;
begin
XXDESCOR.PR_XXDESCOR_TRUNC_UPL_SALESSTK();
commit;
XXDESCOR.PR_XXDESCOR_ANALYZE();
– read data from wwv_flow_files
22_UPLOAD_CSV;
select blob_content into v_blob_data
from wwv_flow_files where name =
v_blob_len := dbms_lob.getlength(v_blob_data);
v_position := 1;
– read and convert binary to char
while ( v_position <= v_blob_len ) loop
v_raw_chunk := dbms_lob.substr(v_blob_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;
– when a whole line is retrieved
if v_char = chr(10) then
– convert comma to : to use wwv_flow_utilities
v_line := replace (v_line, ';', ':');
v_line := replace (v_line, chr(13), '');
v_line := replace (v_line, chr(10), '');
– convert each column separated by : into array of data
v_data_array := wwv_flow_utilities.string_to_table (v_line);
– insert data into target table
execute immediate 'insert into DE_SCOR_UPL_CHAR (RAGIONE_SOCIALE, NAZIONE, CREATED_BY, CREATION_DATE, LAST_UPDATE_BY, LAST_UPDATE_DATE)
values (:1, :2, :3, :4, :5, :6)'
using
trim(v_data_array(1)),
trim(v_data_array(2)),
v_user,
v_load_date,
v_user,
v_load_date;
– clear out
v_line := null;
v_rec_no := v_rec_no 1;
end if;
end loop;
end;
The result on Apex is:
Ferrari Italia ADMIN 20-OTT-09 ADMIN 20-OTT-09
B t & Maskin Svezia ADMIN 20-OTT-09 ADMIN 20-OTT-09
Jrgen Lushgy Norvegia ADMIN 20-OTT-09 ADMIN 20-OTT-09
Gonzales Spagna ADMIN 20-OTT-09 ADMIN 20-OTT-09
I hope it’s enough for you to understand our problems.
If you have any further questions tell me.
Regards.
October 17th, 2009 9:08 pm
This works great, but I have one issue. When the value being inserted into the table has commas, APEX gives me an “invalid number” error. How would I get around that?
October 18th, 2009 9:15 am
Cedrick,
This is the limitation of this code because it needs a delimiter to distinguish each column data. And a comma is commonly used. If you have control over how to get this csv file, you can try to get a different delimiter such as ~ or ^.
October 26th, 2009 12:23 am
hi
when i upload excel file, in database some sort of unreadable charactars are being stored.
in my case group of people will upload excel files.in those excel files 10-15 questions will be present.i want those questions to be added in my database.
October 26th, 2009 8:40 am
Jayanth,
This junk characters are formatting from excel. Adapting from my codes, Advait wrote a post about uploading an excel file into database. You should check it out at http://advait.wordpress.com/2008/05/21/uploading-excel-sheet-using-oracle-application-express-apex/. This may solve your issue.
November 26th, 2009 10:37 am
Hi,
I tried to user your solution, with the following settings:
- A table with 3 columns
- A csv which has been uploaded via an Apex file chooser
Values:
1,2,3
1,2,3
1,2,3
- By trying to use the skript, I get the following error message:
ORA-00928: missing SELECT keyword
- By using exception handling, I narrowed it down to following line:
EXECUTE IMMEDIATE ‘insert into UPLOAD_TEST (1, 2, 3) values (:1,:2,:3)’ USING v_data_array(1), v_data_array(2), v_data_array(3);
Do you know, what the problem can be?
Thank you very much for your help in advance!
Best regards,
Thorben
November 26th, 2009 9:51 pm
Thorben,
I think the column names you specified is not correct. The three values after “insert into UPLOAD_TEST” must be the column names, not numbers.
EXECUTE IMMEDIATE ‘insert into UPLOAD_TEST (col1, col2, col3) values (:1,:2,:3)’ USING v_data_array(1), v_data_array(2), v_data_array(3);
November 27th, 2009 2:48 am
Please, kill me now :/
Ouch…
Thank you very much!
January 15th, 2010 9:39 pm
Hi,
can this tools is used with dblink
i assume like this
“…. 1nsert 1nto DATA_UPLD@DBLINK_NAME …”
January 15th, 2010 10:29 pm
Yes you can if the account of dblink has privilege to do so. Please be cautious about using dblink for DML.
January 26th, 2010 3:04 pm
Hi -
I’m getting the ORA-01403: no data found error because some of my columns contain null values.
How do I get around that?
Thanks,
Matt
January 26th, 2010 10:11 pm
You can try to use nvl function to replace NULL with other value before insertion.
February 12th, 2010 3:17 am
Hi i am working on Apex now.
I try to use your code (copy of my code)
DECLARE
v_blob_data BLOB;
v_blob_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_sr_no number := 1;
BEGIN
– Read data from wwv_flow_files
delete from ZONNEPANEEL_3;
select blob_content into v_blob_data
from wwv_flow_files
where last_updated = (select max(last_updated) from wwv_flow_files where UPDATED_BY = :APP_USER)
and id = (select max(id) from wwv_flow_files where updated_by = :APP_USER);
v_blob_len := dbms_lob.getlength(v_blob_data);
v_position :=1;
– Read and convert binary to char
WHILE ( v_position Column DATUM
38,003 –> EPRODUCTIE
February 12th, 2010 9:16 am
Alex,
It seems like your comment post was cut off.
Ittichai
March 18th, 2010 4:24 am
if the entire last column in the excel is null then it gives the error no_data_found
How do i get around that?
even though i use NVl.It won’t serve the purpose.
Where should i use the nvl.
Note: the problem is only if the entire last column is blank.
March 18th, 2010 7:52 pm
Supriya,
I tried with test data with last column is empty, it can still load data into table. Have you tried to run debug and see where it raised no_data_found exception? Please note that if you run the codes outside APEX, you will get the same exception as well if the following is not set:
wwv_flow_api.set_security_group_id(858728211457679);
The long number is the workspace_id which can be retrieved by querying select WORKSPACE_ID from APEX_WORKSPACES;
Ittichai
May 11th, 2010 11:18 pm
Hi Ittichai,
I’m using the above code to upload a csv into the database and it is showing me this error
ORA-01858: a non-numeric character was found where a numeric was expected.
my table structure is
desc fin_consultant;
Name Null Type
—————————— ——– ———————————————————————————————————————————————————————————————
EMP_NUMBER VARCHAR2(20)
NAME VARCHAR2(400)
LEVEL VARCHAR2(10)
TYPE VARCHAR2(200)
LOCATION VARCHAR2(100)
EFFECTIVE_START_DATE DATE
EFFECTIVE_END_DATE DATE
and the code I’m using is :
DECLARE
v_blob_data BLOB;
v_blob_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;
BEGIN
delete from fin_consultant;
– 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 where UPDATED_BY = :APP_USER)
and id = (select max(id) from wwv_flow_files where updated_by = :APP_USER);
v_blob_len := dbms_lob.getlength(v_blob_data);
v_position := 1;
– Read and convert binary to char
WHILE ( v_position <= v_blob_len ) LOOP
v_raw_chunk := dbms_lob.substr(v_blob_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;
– When a whole line is retrieved
IF v_char = CHR(10) THEN
– Convert comma to : to use wwv_flow_utilities
v_line := substr(v_line, 1, length(v_line)-2);
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);
– Insert data into target table
EXECUTE IMMEDIATE 'insert into FIN_CONSULTANT( EMP_NUMBER,NAME,LEVEL,TYPE,LOCATION,EFFECTIVE_START_DATE,EFFECTIVE_END_DATE)
values (:1,:2,:3,:4,:5,:6,:7)'
USING
v_data_array(1),
v_data_array(2),
v_data_array(3),
v_data_array(4),
v_data_array(5),
to_date(v_data_array(6),'dd/mm/yyyy'),
to_date(v_data_array(7),'dd/mm/yyyy');
– Clear out
v_line := NULL;
END IF;
END LOOP;
END;
Please help.
Regards,
Priyanka
May 12th, 2010 7:51 am
Priyanka,
Does it tell you which line? You can use the development tool like PL/SQL Dev or SQL Dev to debug the code. But please note that data will be visible only when the security group ID is set properly. Please see previous comment on how to do so.
Ittichai
June 23rd, 2010 5:13 am
Hi ittichai,
I have a CSV file with the following data.
1,2,”Name1,Surname1″
3,4,”Name2,Surname2″
5,6,”Name3,Surname3″
but the problem is that the Surnames will not be uploaded since there is a ‘,’ in the middle of the 3rd column.
What is the best way to handle this problem?
is there a particular function which will not replace ‘,’ within the double quotation marks?
July 25th, 2010 8:32 am
Sushi,
You can still try to separate those two fields (Name and Surname), but when you insert the record, you can then concat them like v_data_array(3) || v_data_array(4) in the insert statement.
July 18th, 2010 11:06 am
Hi,
I am having a small issue. In my CSV file I have a text field which sometimes has a colon inside, eg.:
Project:2000
Since we are converting our chosen delimiter to : (colon) for the wwv_flow_utilities.string_to_table function, after processing a line where there is an extra colon (inside a field) the application fails since it expects other values for next columns…
How would you propose to fix this issue assuming that the text inserted into a table whould still have the colon inside it?
Regards,
Pawel.
July 25th, 2010 8:37 am
Pawel,
You can try to convert “:” to another delimiter first, then convert it back to the colon before insert.
– Additional line to replace : to ~
v_line := REPLACE (v_line, ‘:’, ‘~’);
– Perform separation into an array
v_data_array := wwv_flow_utilities.string_to_table (v_line);
– Before insert, convert ~ in a column back to :
v_data_array(3) := REPLACE (v_data_array(3(, ‘~’, ‘:’);
August 11th, 2010 9:24 am
HI,
how can retrive the data from excel sheet to oracle database and i need to perform some dml over there so that it can updated in the excel sheet also. I need solution in detail that mean by using which,how and what
August 11th, 2010 10:26 am
The sample here is for CSV file so you have to do conversion from XLS to CSV first. If you still insist working with Excel, you may try the 3rd party production such as http://www.excel-db.net/Update%20Excel-DB.htm.
August 27th, 2010 1:00 am
Hi,
Uploading File with English character is working fine. But it not supporting for other than English Characters. Can anyone help me to give the solution for this.
Regards
Kumaran V
kumaran.vijayarangan@cognizant.com
August 28th, 2010 9:39 am
Handling international languages is not in the scope of this post. The concept of this post is to read the whole file into a LOB, then read line-by-line, then parses each line with a delimiter. I’m not sure the junk characters you’re talking about are from being processed by the script, or from your database not be able to handle Chinese language. Please check with DBA to see whether or not you can read and write Chinese first.
August 27th, 2010 1:24 am
Hi,
To add to the above issue, when i tried Uploading File with Chinese character the data loaded in the table seems to be junk.
Please Help me if anybody faced the similar problem.
Regards
Kumaran V
kumaran.vijayarangan@cognizant.com
August 29th, 2010 6:50 am
[...] I am working on. Credit goes where credit is due – there are some existing techniques I have seen but I wanted to provide my own implementation, and one that made sense to [...]