How to upload a text file and write into a table in Oracle APEX?

By default, all files using the “File Browse..” element will be uploaded to the “wwv_flow_files” table which has structure as follows:

?

01 02 03 04 05 06 07 08 09 10 11 12 13 14 15 16 17 18 19 20 21 22

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.

?

01 02 03 04 05 06 07 08 09 10 11 12 13 14 15 16 17 18 19 20 21 22 23 24 25 26 27 28 29 30 31 32 33 34 35 36 37 38 39 40 41 42 43

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