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;
/
