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;
/
January 12th, 2012 12:30 am
Hi,
Sir ,can these procedure could be use to retrieve data from XML file.for eg:
- MS Windows 7 32-bit SP1
- 39 °C
- *2.00 GB DDR2*
- 34 °C
These is the XML data,
now I want to retrieve data of RAM from these XML file i.e 2.00 GB DDR2 and push it into the table through APEX..
how could I do that,pls suggest.
January 12th, 2012 9:36 am
I’ve written a post about loading XML from URL into database before. Why don’t you check it out and see whether or not it may be beneficial to your need?
http://oraexplorer.com/2007/10/read-xml-data-from-url-and-insert-into-oracle/
January 18th, 2012 4:26 am
Hi,
I am using this code as it is in my apex environment to load the data from csv to database table but facing the error as NO DATA found.
I have did some exception handling and able to know that my select query is always get the data while selecting blob content from file.
I have created one file browse and submit button on the page. The button is branched to same page after submission.
There are 2 process I used on submit after computation and validation
1. This process is dumping the csv file to my attachment table.
58_FILE is not null ) then
58_FILE;
It is working fine –
==========================================
declare
file_num number(8);
begin
select UPLOAD_FILE_TEST_seq.nextval into file_num from dual;
if (
insert into UPLOAD_FILE_TEST(file_no,file_name,file_container,mime_type)
select file_num,filename, blob_content, mime_type from wwv_flow_files where name =
delete wwv_flow_files where name =
58_FILE;
end if;
end;
============================================================
2. The second process I used your code and getting issues of NO DATA FOUND -
=======================================================
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
select FILE_CONTAINER into v_blob_data from (select * from UPLOAD_FILE_TEST order by file_no desc)
where rownum < 2;
v_blob_len := dbms_lob.getlength(v_blob_data);
v_position := 1;
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;
IF v_char = CHR(10) THEN
v_line := REPLACE (v_line, ',', ':');
v_data_array := wwv_flow_utilities.string_to_table (v_line);
EXECUTE IMMEDIATE 'insert into DATA_FILE (name, subject, marks, grade) values (:1,:2,:3,:4)'
USING
v_data_array(1),
v_data_array(2),
v_data_array(3),
v_data_array(4);
v_line := NULL;
END IF;
END LOOP;
END;
=================================
3. hex_to_decimal function code -
create or replace function hex_to_decimal
( p_hex_str in varchar2 ) return number
is
v_dec number;
v_hex varchar2(16) := '0123456789ABCDEF';
begin
v_dec := 0;
for indx in 1 .. length(p_hex_str)
loop
v_dec := v_dec * 16 + instr(v_hex,upper(substr(p_hex_str,indx,1)))-1;
end loop;
return v_dec;
end hex_to_decimal;
==================
The early response is highly appreciated.
Thanks
Jitendra
January 19th, 2012 5:00 pm
Have you done all of these (especially the step where you got error) outside APEX? I assume you’ve checked that there is new record created in your custom table when file is uploaded using the File Browser. The easiest way if you’re on APEX 4 is to specify a custom BLOB-column table http://oraexplorer.com/2011/03/file-browser-in-apex-4-with-blob-column-specified-in-item-source-attribute/
January 19th, 2012 11:44 pm
Hi Ittichai,
Thanks for the response.
I have done all these steps in side apex only. I am not facing any issue while browsing the file and putting it to the custom table. Even I have tried your code as it is to read the file from wwv_flow_files but facing the same error as NO DATA FOUND.
I think the issue is when we try to read the file and insert the csv data to database tables ?
Can you plesse suggest here or provide some other step by step example to upload the csv data to data base table.
Thanks
Jitendra
January 21st, 2012 11:08 pm
You should try to run it outside APEX in the tools like SQL Developer, TOAD, or PL/SQL Developer which have the debug (step through) functions. (Make sure you set the security first.) This way you can step through you codes and watch variables’ values to identify which line really is the root cause.