Loading CSV(Comma Delimited Values) document into a Sample Oracle APEX Application

Today i want to show you a way you can load .csv documents into tables in an application.

For this session i have a sample application that stores information into this these tables

1.EMP ----these stores a list of Employees

2.BADLOG---this logs errors  


3.DOCUMENT_TABLE --this stores the uploaded document(.csv in this case)


I want to load a .csv(comma delimited values) document into the EMP table.
Note: The document must have matching columns with the table you want to upload into 

Steps to take on the application level
1.Create a Page that 'll allow the user to upload the CSV into the document table
2.On the same page or a branched page ensure you call this procedure which must already be in the database

/****************************************************************/



procedure document_upload_api

 (

  p_filename in varchar2,

  p_output out varchar2

 ) is

v_blob_data BLOB;

 v_blob_length NUMBER; 

v_position NUMBER; 

v_inserted_rows NUMBER:=0;

v_output VARCHAR2(3000);

v_raw_chunk RAW(10000); 

v_char CHAR(1); 

v_count NUMBER:=0;

c_chunk_length NUMBER := 1; 

v_line VARCHAR2 (32767) := NULL; 

l_errmsg VARCHAR2(300);

l_errmsg0 VARCHAR2(300);

v_data_array wwv_flow_global.vc_arr2; 

BEGIN 

-- Read data from sample uploaded csv document

select doc_blob_content into v_blob_data 

from document_table where filename = p_filename; 

v_blob_length := dbms_lob.getlength(v_blob_data); 

v_position := 1; 

--Empty Log Table

EXECUTE IMMEDIATE 'delete from badlog';

-- Read and convert binary to char 

WHILE ( v_position <= v_blob_length ) LOOP 

v_raw_chunk := dbms_lob.substr(v_blob_data,c_chunk_length,v_position); 

v_char := chr(hex2dec(rawtohex(v_raw_chunk))); 

v_line := v_line || v_char; 

v_position := v_position + c_chunk_length; 

-- Retrieve a whole line

IF v_char = CHR(10) THEN 

Begin

-- Convert comma to : to use wwv_flow_utilities 

v_line := REPLACE(REPLACE(REPLACE (v_line, ',', ':'),chr(13),''),chr(10),''); ---remove carriage returns

v_count:=v_count+1;

-- Convert each column separated by : into an array of data 

v_data_array := wwv_flow_utilities.string_to_table (v_line); 

-- Insert data into target table --EMP

EXECUTE IMMEDIATE 'insert into EMP(EMPNO,ENAME,JOB,MGR,HIREDATE,SAL,COMM,DEPT,EMAIL) 

values (:1,:2,:3,:4,:5,:6,:7,:8,:9)' 

USING 

v_data_array(1), 

v_data_array(2), 

v_data_array(3), 

v_data_array(4), 

v_data_array(5),

v_data_array(6), 

v_data_array(7), 

v_data_array(8),

v_data_array(9); 

v_inserted_rows := v_inserted_rows + SQL%ROWCOUNT;

commit;

-- Get Log of errors

exception

when others then

l_errmsg0 := sqlerrm;

--Check for corresponding comprehensive error message

begin

select search_condition into l_errmsg

from user_constraints

where table_name='EMP'

and instr(l_errmsg0,constraint_name,1)>0;

--This will make the error message a bit comprehensive

 insert into badlog ( errm, data )

  values ( l_errmsg, v_line );

  commit;

exception

 when others then

 insert into ts_badlog ( errm, data )

  values ( 'An Error Occured', v_line );

  commit;

  end;

End;

-- Clear out 

v_line := NULL; 

END IF; 

END LOOP; 

commit;

v_output:= 'This document contains '||v_count||' records ......'||v_inserted_rows||' records inserted ';

if v_inserted_rows<v_count then

p_output:=v_output||' ..Please Check Log';

else

p_output:=v_output;

end if;

END document_upload_api; 

3.You can view a log of possible errors by creating a report page based on the badlog table





Hope you find this helpful