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_upld
Name Null? Type
—————————————– ——– —————————-
CODE_ID NOT NULL VARCHAR2(20)
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_upld; -- 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_UPLD (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.
Hope this helps !!