APEX to upload a text file and write into a table

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:

[source language=”sql”]
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
[/source]

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.

[source language=”sql”]
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;
/
[/source]

113 thoughts on “APEX to upload a text file and write into a table”

  1. 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.

  2. 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.
    It is working fine —
    ==========================================
    declare
    file_num number(8);
    begin
    select UPLOAD_FILE_TEST_seq.nextval into file_num from dual;
    if ( :P58_FILE is not null ) then
    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 = :P58_FILE;

    delete wwv_flow_files where name = :P58_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

      1. 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

        1. 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.

  3. Hi,One of my columns in the csv file contains data with double quotes.For eg:16/9″.
    When this data is being read,it is being read as “9/16″””.
    Is there any way to overcome this?

    Thanks.

    1. Hi,One of my columns in the csv file contains data with double quotes.For eg:9/16″.
      When this data is being read,it is being read as “9/16″””.
      Is there any way to overcome this?

      Thanks.

  4. Hi,One of my columns in the csv file contains data with double quotes.For eg:9/16″.
    When this data is being read,it is being read as “9/16″”” and inserted into the table as “9/16″”” .
    Is there any way to overcome this?

    Kindly reply.

  5. Hello,

    Your code has worked well for me so far; but I have question regarding the SQL. Is it possible for the INSERT statement to have a SELECT query join to the array?

    Overly-simplified, generic example:

    INSERT INTO tab2 (
    name)
    SELECT :1
    FROM tab1 x
    , array y
    WHERE x.id = y.id;

    Tx!

  6. Thanks for the idea. That makes perfect sense! However, now I’m getting a character to number conversion error, and I’m wondering if it’s because of how I am arranging the array in relation to the bind variables:

    52 EXECUTE IMMEDIATE ‘INSERT INTO market_values (
    53 account_id
    54 , reporting_date
    55 , market_value)
    56 SELECT account_id
    57 , ”30-JUL-12”
    58 , :3
    59 FROM accounts
    60 WHERE bank_id = :1’
    61 USING v_data_array(1)
    62 , TO_NUMBER(v_data_array(3));

    The bank_id (VARCHAR2(20)) comes from a regular table. The reporting_date (DATE) value is static. The market_value (NUMBER 19,6) value is what is coming from the array. I receive this error when the code executes:

    ORA-06502: PL/SQL: numeric or value error: character to number conversion error
    ORA-06512: at line 52
    06502. 00000 – “PL/SQL: numeric or value error%s”

    I tried inverting the 1 and 3 array value placement in the USING clause, but that didn’t work either.

    Thanks!
    -Seth.

  7. Hmmm… I think I know what it might be. Every comma is replaced by a comma to delineate columns. Could it be that a valid commas contained in quotation marks in the CSV are throwing off the column separation?

  8. Yup. That’s what it is. I sent that SELECT statement to DBMS_OUTPUT.PUT_LINE. How can I tell your script to accept valid commas enclosed by quotation marks?

  9. I tried doing this, but I get an error saying “ORA-01006: bind variable does not exist”

    I have created the table, function and procedure as mentioned, although it did not work. After a bit of debugging I noticed the failure when I upload the file is during the INSERT statement below:
    EXECUTE IMMEDIATE ‘insert into XXFNE_DEAL_HEAD (BUYER, SUPPLIER, AMOUNT)
    values (:1, :2, :3)’ USING v_data_array(1), v_data_array(2), v_data_array(3) ;

    If i replace the bind variable above with actual values, it works fine.

    Could you please help me fix this?

    1. Chris,

      In addition to what ittichai said, you may also want to check your data types. If AMOUNT is indeed NUMBER, then you may need to modify the third bind variable.

      TO_NUMBER(v_data_array(3))

  10. Pingback: Oracle APEX upload a text file and write into a table « OraPedia

  11. Marcello Yamaguchi

    In my code it is not saving the last line of the csv

    help me?

    PROCEDURE STL_EXTRAIR_ASSINANTE(p_nome_arquivo in varchar2
    , p_id_req in number) is

    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;
    v_rows_loaded NUMBER;

    BEGIN

    — Read data from wwv_flow_files
    select BLOB_CONTENT into v_blob_data
    from wwv_flow_files
    where name = p_nome_arquivo;

    v_blob_len := dbms_lob.getlength(v_blob_data);
    v_position := 1;

    — Read and convert binary to char
    WHILE ( v_position 1 THEN
    INSERT INTO sittel.stl_assinante(nm_assin, cpf, cnpj, documento, id_req, id)
    VALUES (v_data_array(1), v_data_array(2),v_data_array(3), v_data_array(4), p_id_req, sittel.SEQ_STL_ASSINANTE.nextval);
    –END IF;
    — Clear out
    v_line := NULL;
    –v_sr_no := v_sr_no + 1;
    END IF;
    END LOOP;
    commit;
    END;

    1. >> In my code it is not saving the last line of the csv
      It did not insert? For SEQ_STL_ASSINANTE.nextval, I think you might be better using before-insert trigger.

  12. Marcello Yamaguchi

    Sorry…. code correct:

    /*
    select *
    from wwv_flow_files
    order by 1 desc
    */

    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_rows_loaded NUMBER;

    BEGIN
    delete apex_log;
    — Read data from wwv_flow_files
    select BLOB_CONTENT into v_blob_data
    from wwv_flow_files
    where name = ‘F15575/assinante.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
    /*
    INSERT INTO sittel.stl_assinante(nm_assin, cpf, cnpj, documento, id_req, id)
    VALUES (v_data_array(1), v_data_array(2),v_data_array(3), v_data_array(4), p_id_req, sittel.SEQ_STL_ASSINANTE.nextval);
    */
    INSERT INTO apex_log values (v_data_array(1));
    — Clear out
    v_line := null;
    END IF;
    END LOOP;
    END;

  13. Hi Ittichai,

    when I am uploading the file to load the data into the table. I am getting the below error.

    ORA-06550: line 28, column 26: PLS-00201: identifier ‘HEX_TO_DECIMAL’ must be declared ORA-06550: line 28, column 7: PL/SQL: Statement ignored

Leave a Reply to Marcello Yamaguchi Cancel Reply

Your email address will not be published. Required fields are marked *

This site uses Akismet to reduce spam. Learn how your comment data is processed.

Scroll to Top