APEX to upload a text file and write into a table

by Naresh Konda SEO, Digital Marketing, WordPress Designer

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;