declare
v_blob_data blob;
v_clob_data clob;
v_blob_len pls_integer;
v_clob_len pls_integer;
v_position pls_integer;
v_line_end_pos pls_integer;
v_chunk_len pls_integer;
v_loop_count pls_integer:=0;
v_line varchar2(32767) := null;
v_data_array wwv_flow_global.vc_arr2;
v_sr_no number := 1;
v_filename varchar2(4000);
v_file_count pls_integer := 0;
v_mime_type varchar2(512);
v_charset varchar2(32);
v_char_sperator varchar2(1):=:P1_SEPERATOR ; -- ';';
v_starttime timestamp := systimestamp;
v_Seconds number;
v_rec_count pls_integer;
v_message varchar2(4000) := 'Prozessing File Insert' || '
';
--
v_l pls_integer := 0;
v_rchar varchar2(10) := '-';
v_rbefore varchar2(10) := '-';
v_match boolean := false;
-- PL/SQL function to convert a BLOB to a CLOB
function convertToClob(p_blob_in in blob, p_charset varchar2)
return clob
as
v_clob clob;
v_dest_offset number := 1;
v_src_offset number := 1;
v_lang_context number := 0;
v_warning number;
begin
dbms_lob.CREATETEMPORARY(v_clob, true);
dbms_lob.converttoclob(v_clob
,p_blob_in
,dbms_lob.lobmaxsize
,v_dest_offset
,v_src_offset
,nls_charset_id(p_charset)
,v_lang_context
,v_warning);
return v_clob;
end convertToClob;
-- get the runtime information
function getRunTime ( p_starttime timestamp)
return number
is
v_endtime timestamp;
v_runtime interval day to second;
v_Seconds number;
begin
-- get the runtime of the load process
v_endtime := systimestamp;
v_runtime := v_endtime - p_starttime;
select extract(day from v_runtime) * 86400
+ extract(hour from v_runtime) * 3600
+ extract(minute from v_runtime) * 60
+ extract(second from v_runtime)
into v_Seconds
from dual;
return v_Seconds;
end getRunTime ;
begin
-- check if parameter exits
if :P1_FILENAME is not null
then
v_charset := nvl( :P1_CHARSET, 'WE8ISO8859P15');
v_message :=
v_message
|| ' Start load import file '
|| :P1_FILENAME
|| '
with the charset '
|| :P1_CHARSET
|| '
';
-- check if the file exits
select count(ID)
into v_file_count
from apex_application_temp_files
where name = :P1_FILENAME;
-- check if the upload was sucessfull
if v_file_count > 0
then
-- clean the local stage table
delete CSV_STAGE_TAB;
commit;
-----------------------------------------------
-- Read data from apex_application_temp_files
select blob_content, mime_type
into v_blob_data, v_mime_type
from apex_application_temp_files
where name = :P1_FILENAME;
-- read the data into a clob
dbms_lob.CREATETEMPORARY(v_clob_data, true);
v_clob_data := convertToClob(p_blob_in => v_blob_data, p_charset => v_charset);
v_blob_len := dbms_lob.getlength(v_blob_data);
v_clob_len := dbms_lob.getlength(v_clob_data);
v_position := 1;
v_message :=
v_message || ' Read ' || to_char(v_blob_len) || ' byte and ' || to_char(v_clob_len) || ' characters
';
-- check again if stage table is empty
-- check that this table is empty
select count(*) into v_rec_count from CSV_STAGE_TAB;
if v_rec_count > 0 then
RAISE_APPLICATION_ERROR (-20101, 'Stage Table is not empty!! Stop processing the file !');
end if;
-- parse the file
while (v_position <= v_clob_len)
loop
-- use bigger chunks to avoid problem with substr
-- reading on be one is to with DBMS_LOB.substr is very slow!
v_line_end_pos:=DBMS_LOB.instr(v_clob_data,chr(10),v_position,1)+1;
-- if v_line_end_pos is 0 then throw exception
--
if v_line_end_pos = 0 then
raise_application_error( -20001 , '-- Error :: Can not upload CSV files with linesize >>32767 or without line end chr(10) Char' );
end if;
v_chunk_len:= v_line_end_pos - v_position;
v_line := dbms_lob.substr(v_clob_data , v_chunk_len, v_position );
-- auf die nächste Position weiterschalten
v_position := v_position + v_chunk_len;
-- v_message :=
-- v_message
-- || ' DEBUG v_line_end_pos::' ||to_char(v_line_end_pos)||' - v_chunk_len::'||to_char(v_chunk_len)||' - v_position::'||to_char(v_position)||'';
-- get the lenght
v_l := length(v_line);
-- read the string
for i in 1 .. v_l
loop
v_rchar := substr(v_line, i, 1);
if v_rchar ='"' and v_rbefore=v_char_sperator then
v_match:=true;
end if;
if v_rchar =v_char_sperator and v_rbefore='"' then
v_match:=false;
end if;
if v_rchar =v_char_sperator and v_match
then
v_line:=substr(v_line,1,i-1)||'^'||substr(v_line,i+1,10000);
end if;
v_rbefore:=v_rchar;
end loop;
-- replace all "
v_line := replace(v_line, '"', '');
-- Convert comma to : to use the wwv_flow_utilities
v_line := replace(v_line, v_char_sperator, ':');
-- get ',' in text fields back
v_line := replace(v_line, '^', ',');
-- Convert each column separated by : into array of data
v_data_array := wwv_flow_utilities.string_to_table(v_line);
-- fill the missing records to get the 30 values
for y in 1 .. 30
loop
if v_data_array.exists(y)
then
null;
else
-- I use this strange sign to filter out the data later in this ETL process
v_data_array(y) := '#';
--debug
--v_data_array(29) := v_mime_type;
end if;
end loop;
-- insert into the stage table DWH_OPERATION.COMET_CONTRACT_INPUT3@DEDWHOPERATION
insert into CSV_STAGE_TAB(ID
,IMPORTFILENAME
,FIELD01
,FIELD02
,FIELD03
,FIELD04
,FIELD05
,FIELD06
,FIELD07
,FIELD08
,FIELD09
,FIELD10
,FIELD11
,FIELD12
,FIELD13
,FIELD14
,FIELD15
,FIELD16
,FIELD17
,FIELD18
,FIELD19
,FIELD20
,FIELD21
,FIELD22
,FIELD23
,FIELD24
,FIELD25
,FIELD26
,FIELD27
,FIELD28
,FIELD29
,FIELD30)
values (v_sr_no
,:P1_FILENAME
,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_data_array(10)
,v_data_array(11)
,v_data_array(12)
,v_data_array(13)
,v_data_array(14)
,v_data_array(15)
,v_data_array(16)
,v_data_array(17)
,v_data_array(18)
,v_data_array(19)
,v_data_array(20)
,v_data_array(21)
,v_data_array(22)
,v_data_array(23)
,v_data_array(24)
,v_data_array(25)
,v_data_array(26)
,v_data_array(27)
,v_data_array(28)
,v_data_array(29)
,v_data_array(30));
-- reset line
v_line := null;
-- rember line count
v_sr_no := v_sr_no + 1;
if mod(v_sr_no, 100) = 0
then
commit;
end if;
-- free the collecio
v_data_array.DELETE;
end loop;
commit;
v_seconds:=getRunTime ( p_starttime => v_starttime);
v_message := v_message || '
Insert into stage ' || to_char(v_sr_no)
|| ' rows after '|| to_char(v_Seconds, '999G990D099')
|| ' Seconds';
else
v_message := v_message || ' Can not find file with the name :: ' || v_filename;
end if;
end if;
-- get the runtime of the load process
-- get the runtime of the load process
--
v_seconds:=getRunTime ( p_starttime => v_starttime);
v_message := v_message || '
File load needs ' || to_char(v_Seconds, '999G990D099') || ' sec';
apex_application.g_print_success_message := '' || v_message || '';
exception
when others
then
v_message := v_message || ' ' || sqlerrm;
apex_application.g_print_success_message := '' || v_message || '';
end;