Hello Experts,

I have a below code from where I want to convert .xls file to .csv file and load the data into pl/sql tables but I am not able to perform it.

Could you please help to get this done?

Code:

CREATE OR REPLACE FUNCTION LOAD_CSV ( p_table in varchar2,
p_dir in varchar2 DEFAULT 'ORA_DIR' ,
P_FILENAME in varchar2,
p_ignore_headerlines IN INTEGER DEFAULT 1,
p_delimiter in varchar2 default ',',
p_optional_enclosed in varchar2 default '"' )
return number
is
l_input utl_file.file_type;
l_theCursor integer default dbms_sql.open_cursor;
l_lastLine varchar2(4000);
l_cnames varchar2(4000);
l_bindvars varchar2(4000);
l_status integer;
l_cnt number default 0;
l_rowCount number default 0;
l_sep char(1) default NULL;
L_ERRMSG varchar2(4000);
V_EOF BOOLEAN := false;
l_filename varchar2(500) := P_FILENAME||'.csv';

begin

utl_file.frename ('ORA_DIR',P_FILENAME,'ORA_DIR',l_FILENAME,TRUE);

l_cnt := 1;
for TAB_COLUMNS in (
select column_name, data_type from user_tab_columns where upper(table_name) = upper(p_table) order by column_id
) loop
l_cnames := l_cnames || tab_columns.column_name || ',';
l_bindvars := l_bindvars || case when tab_columns.data_type in ('DATE', 'TIMESTAMP(6)') then 'to_date(:b' || l_cnt || ',"dd-mon-yyyy hh24:mi:ss"),' else ':b'|| l_cnt || ',' end;

l_cnt := l_cnt + 1;
end loop;
l_cnames := rtrim(l_cnames,',');
L_BINDVARS := RTRIM(L_BINDVARS,',');

--L_INPUT := UTL_FILE.FOPEN( P_DIR, P_FILENAME, 'r',5000);
L_INPUT := UTL_FILE.FOPEN( P_DIR, l_FILENAME, 'r',5000);
IF p_ignore_headerlines > 0
THEN
BEGIN
FOR i IN 1 .. p_ignore_headerlines
LOOP
UTL_FILE.get_line(l_input, l_lastLine);
END LOOP;
EXCEPTION
WHEN NO_DATA_FOUND
THEN
v_eof := TRUE;
end;
END IF;

if not v_eof then
dbms_sql.parse( l_theCursor, 'insert into ' || p_table || '(' || l_cnames || ') values (' || l_bindvars || ')', dbms_sql.native );

loop
begin
utl_file.get_line( l_input, l_lastLine );
exception
when NO_DATA_FOUND then
exit;
end;

if length(l_lastLine) > 0 then
for i in 1 .. l_cnt-1
LOOP

dbms_sql.bind_variable( l_theCursor, ':b'||i,
rtrim(rtrim(ltrim(ltrim(
REGEXP_SUBSTR(l_lastline,'(^|,)("[^"]*"|[^",]*)',1,i),p_delimiter),p_optional_enclosed),p_delimiter),p_optional_enclosed));
end loop;
begin
l_status := dbms_sql.execute(l_theCursor);
l_rowCount := l_rowCount + 1;
exception
when OTHERS then
L_ERRMSG := SQLERRM;
insert into BADLOG ( TABLE_NAME, ERRM, data, ERROR_DATE )
values ( P_TABLE,l_errmsg, l_lastLine ,to_date(sysdate,'dd-mon-yyyy hh24:mi:ss') );
commit;
end;
end if;
end loop;

dbms_sql.close_cursor(l_theCursor);
utl_file.fclose( l_input );
commit;
end if;
--insert into IMPORT_HIST (FILENAME,TABLE_NAME,NUM_OF_REC,IMPORT_DATE)
--values ( P_FILENAME, P_TABLE,l_rowCount,sysdate );

insert into IMPORT_HIST (FILENAME,TABLE_NAME,NUM_OF_REC,IMPORT_DATE)
values ( l_FILENAME, P_TABLE,l_rowCount,sysdate );

commit;
RETURN L_ROWCOUNT;
dbms_output.put_line ('Total Records: '||L_ROWCOUNT);
end LOAD_CSV;

----------Wrapper to test above code----------------------------
DECLARE
p_table VARCHAR2(200) := 'temp_part';
p_dir VARCHAR2(200) := 'ORA_DIR';
p_filename VARCHAR2(200) := 'b.xls';
p_ignore_headerlines integer := 1;
p_delimiter varchar2(2) := ',';
p_optional_enclosed varchar2(2) := '"';
v_return NUMBER;
BEGIN
v_return := LOAD_CSV(p_table => p_table
,p_dir => p_dir
,p_filename => p_filename
,p_ignore_headerlines => p_ignore_headerlines
,p_delimiter => p_delimiter
,p_optional_enclosed => p_optional_enclosed);

dbms_output.put_line('v_return = ' || v_return);
END;

Thanks,
Ankit.