Friday, 15 January 2016
Following is the table detail which I want to populate from data in excel sheet.
Table Name : UPLOAD_DATA
Please follow below steps :
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 UPLOAD_DATA table. My excel sheet content looks as shown below.
create or replace procedure "INSERT_ROW"
(
P_RECORD IN VARCHAR2
) AS
v_col_1 varchar2(100);
v_col_2 varchar2(100);
v_col_3 varchar2(100);
BEGIN
v_col_1 := substr(P_RECORD,1,instr(P_RECORD,',',1,1)-1);
v_col_2 := substr(P_RECORD,instr(P_RECORD,',',1,1)+1,instr(P_RECORD,',',1,2)-instr(P_RECORD,',',1,1)-1);
v_col_3 := substr(P_RECORD,instr(P_RECORD,',',1,2)+1,length(P_RECORD)-instr(P_RECORD,',',1,2));
--dbms_output.put_line('rECORD - ' ||v_col_1||' , '||v_col_2||' , '||v_col_3);
insert into UTR_OUTCOME(DESCRIPTION,STATUS,END_ON) values(v_col_1,v_col_2,v_col_3);
commit;
--dbms_output.put_line(v_col_5);
exception
when others then
--dbms_output.put_line('Error : '||SQLERRM);
insert into UTR_OUTCOME(DESCRIPTION) values('Error');
end;
Below is the code for uploading the excel sheet.
declare
v_temp BLOB;
v_record varchar2(3276);
v_len number := 0;
v_count number:=0;
v_data varchar2(32767);
begin
-- When an data is uploaded it is stored in the
-- FLOW_FILES.WWV_FLOW_FILE_OBJECTS$ table.
-- The wwv_flow_file_objects$ table is identified in the dads.conf
-- file in the PlsqlDocumentTablename parameter.
-- WWV_FLOW_FILES is a view to this table.
--File is uploaded in BLOB format so we need to extract data from it and insert into our table.
select blob_content into v_temp
from wwv_flow_files
where
created_on= (select max(created_on) from wwv_flow_files where UPDATED_BY = :app_user)
and id = (select max(id) from wwv_flow_files where updated_by = :app_user);
v_data := UTL_RAW.CAST_TO_VARCHAR2(DBMS_LOB.SUBSTR(v_temp,DBMS_LOB.GETLENGTH(v_temp), 1));
DBMS_OUTPUT.PUT_LINE('THE LENGTH IS : ' || DBMS_LOB.GETLENGTH(v_temp));
DBMS_OUTPUT.PUT_LINE('THE BLOB IS READ : ' || v_data );
v_data := substr(v_data,instr(v_data,chr(10),1)+1,length(v_data));
v_len := length(v_data);
DBMS_OUTPUT.PUT_LINE(v_len);
delete from utr_outcome;
while v_count <= v_len-1
loop
v_count := v_count+1;
v_record := substr(v_data,v_count,instr(v_data,chr(10),v_count)-v_count);
DBMS_OUTPUT.PUT_LINE(v_record);
INSERT_ROW(v_record);
v_record :='';
v_count :=instr(v_data,chr(10),v_count);
DBMS_OUTPUT.PUT_LINE(v_count);
end loop;
Exception
When others then
DBMS_OUTPUT.PUT_LINE('Error : '||sqlerrm);
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 !! 
