Uploading excel sheet using Oracle Application Express (APEX)

Introduction:
Some times we need the flexibility to upload the data to a table from excel 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.

Implementing data upload using excel sheet

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

SQL> desc data_upload
Name Null? Type
—————————————– ——– —————————-
SR_NO NUMBER
CODE_ID NOT NULL VARCHAR2(20)
CODE_CTGY NUMBER
CTGY_NAME VARCHAR2(20)
CTGY_DESC VARCHAR2(20)
ITEM_NAME VARCHAR2(100)
ITEM_DESC VARCHAR2(512)
ITEM_UNIT VARCHAR2(20)
ACTUL_COST NUMBER
DIS_PER NUMBER
SCHM_ID NUMBER
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 excel 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 excel 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 excel 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 excel sheet is uploaded. All other columns are metadata about excel.
When we upload an excel 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.
In my case, I want to upload the rows into data_upld table. My excel sheet content looks as shown below.

I want to upload the above 6 rows.
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 excel 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 data_upload;   -- 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 DATA_UPLOAD (SR_NO, CODE_ID, CODE_CTGY, CTGY_NAME, CTGY_DESC, ITEM_NAME, ITEM_DESC,  ACTUL_COST, DIS_PER,SCHM_ID, ITEM_UNIT)  values (:1,:2,:3,:4,:5,:6,:7,:8,:9,:10,:11)'  USING  v_sr_no,  v_data_array(1),  to_number(v_data_array(2)),  v_data_array(3),  v_data_array(4),  v_data_array(5),  v_data_array(6),  to_number(v_data_array(7)),  to_number(v_data_array(8)),  to_number(v_data_array(9)),  v_data_array(10);  -- 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 excel sheet will get loaded into table.


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