Muhammad Nadeem Chaudhry's Blog


Uploading CSV using Oracle Application Express (APEX)

Posted in Uncategorized by Muhammad Nadeem Chaudhry on July 18, 2011
Sometimes we need the flexibility to upload the data to a table from CSV sheet. The can be implemented very well in JSP and other application. I was having this requirement to implement the same in Oracle Application Express (APEX).

I was going though one of the blog by Ittichai, for the same and he has given a very clean steps for getting this work. I followed the steps and it worked for me. I have documented the similar steps below as per my understanding. Below are the details.

Pre-requisite:

File should be in CSV Format.

First you will need to identify the workspace ID.

SQL> col WORKSPACE_ID format 999999999999999

SQL> select workspace, schemas, workspace_id from apex_workspaces;

You won’t be able to see anything yet.

SQL> select id, flow_id, name, filename from wwv_flow_files;

But after setting the workspace ID, you should be able to see its contents.

SQL> exec wwv_flow_api.set_security_group_id(‘858728211457679’);

PL/SQL procedure successfully completed

SQL> select id, flow_id, name, filename from wwv_flow_files;

Implementing data upload using CSV sheet

Following is the table detail which I want to populate from data in CSV sheet.

SQL> DESC Table_Name;

1) Created a “File Browse” item on a page and having internal name it P35_UPLOAD.

2) Created a button having internal name as “Upload“. This button is used to upload and process the data in the CSV sheet.

3) Created a conditional branch when upload button is pressed, it should branch to the same page (35 in my case)

4) When we select an CSV sheet using browse button and click on submit button, it will branch to same page (indirectly we are saying that its going to refresh the page). In such case even if there is no code written in the backend, APEX is going to load the CSV sheet in a BLOB format into a table wwv_flow_files.

This is the internal table used by APEX. Our task is to read this table and get the required row which got inserted into this table. With each upload 1 row will get inserted into the table. One of the column of the table (BLOB_CONTENT) is BLOB where the actual CSV sheet is uploaded. All other columns are metadata about CSV.

When we upload an CSV sheet, a random name will get generated for the file in the form FXXXXX/<file_name_you_uploaded>. You can use this to get the file details. Also when you refresh the page, it will upload the file to this table wwv_flow_files, but as soon as refresh completed and it displays the page back again, that row will get deleted again. So you have to get that row processed immediately using the code that you will write when submit button is pressed.

5) Following is the code for the same

Before using the below code, make sure you have a function hex_to_decimal  in your database. The code for the same is as given below. 

create or replace function hex_to_decimal 

–this function is based on one by Connor McDonald 

http://www.jlcomp.demon.co.uk/faq/base_convert.html 

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

Below is the code for uploading the CSV sheet. 

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_sr_no number := 1; 

BEGIN

delete from MACS; 

      — Read data from wwv_flow_files</span> 

Select blob_content into v_blob_data 

from wwv_flow_files 

where last_updated = (select max(last_updated) from wwv_flow_files where UPDATED_BY = :APP_USER) 

and id = (select max(id) from wwv_flow_files where updated_by = :APP_USER); 

v_blob_len := dbms_lob.getlength(v_blob_data); 

v_position := 1; 

     — Read and convert binary to char</span> 

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

IF v_char = CHR(10) THEN

    — Convert comma to : to use wwv_flow_utilities </span> 

v_line := REPLACE (v_line, ‘,’, ‘:’); 

    — Convert each column separated by : into array of data </span> 

v_data_array := wwv_flow_utilities.string_to_table (v_line); 

   — Insert data into target table </span> 

EXECUTE IMMEDIATE ‘insert into MACS (ITEM,MAC) 

values (:1,:2)’

USING 

v_sr_no, 

v_sr_no;

    — Clear out 

v_line := NULL; 

v_sr_no := v_sr_no + 1; 

END IF; 

END LOOP; 

END;

Create a new PLSQL process under Processes section in APEX and put this code under the same. Make the process run when upload button is pressed. Data in CSV sheet will get loaded into table. Hope this helps !!

Reference:

http://oraexplorer.blogspot.com/2007/11/apex-to-upload-text-file-and-write-into.html

2 Responses to 'Uploading CSV using Oracle Application Express (APEX)'

Subscribe to comments with RSS or TrackBack to 'Uploading CSV using Oracle Application Express (APEX)'.

  1. Charls said,

    I am facing one issue here. In one of my column there is a comma seperated data and while trying to insert, it is getting inserted into different columns. How to overcome this situation.? please help me to resolve this.


    • Charles,
      The problem with your code is in below lines of code:

      EXECUTE IMMEDIATE ‘insert into MACS (ITEM,MAC)
      values (:1,:2)’
      USING
      v_data_array(1),
      v_data_array (2);

      Check the order of columns in your CSV files & database table.


Leave a Reply

Fill in your details below or click an icon to log in:

WordPress.com Logo

You are commenting using your WordPress.com account. Log Out /  Change )

Twitter picture

You are commenting using your Twitter account. Log Out /  Change )

Facebook photo

You are commenting using your Facebook account. Log Out /  Change )

Connecting to %s


%d bloggers like this: