Nov 30 2007

APEX to upload a text file and write into a table

Category: APEXittichai @ 9:32 pm

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

Tags:

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

  1. Shirish says:

    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. Jitendra says:

    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

    • ittichai says:

      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/

      • Jitendra says:

        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

        • ittichai says:

          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. sankul says:

    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.

    • sankul says:

      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. sankul says:

    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. Seth says:

    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. Seth says:

    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. Seth says:

    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. Seth says:

    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. Chris says:

    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?

    • ittichai says:

      It sounds like you may not supply values of v_data. Can you please post your codes?

    • Seth says:

      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. Oracle APEX upload a text file and write into a table « OraPedia says:

    […] The article direct link  Oracle APEX upload a text file and write into a table […]

  11. Marcello Yamaguchi says:

    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;

    • ittichai says:

      >> 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 says:

    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. sureshreddy maram says:

    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