A little bug of debugging
SQL> host cat c:\temp\scott.csv
"EMPNO","ENAME","JOB","MGR","HIREDATE","SAL","COMM","DEPTNO"
7369,"SMITH","CLERK",7902,17/DEC/80,800,,20
7499,"ALLEN","SALESMAN",7698,20/FEB/81,1600,300,30
7521,"WARD","SALESMAN",7698,22/FEB/81,1250,500,30
7566,"JONES","MANAGER",7839,02/APR/81,2975,,20
7654,"MARTIN","SALESMAN",7698,28/SEP/81,1250,1400,30
7698,"BLAKE","MANAGER",7839,01/MAY/81,2850,,30
7782,"CLARK","MANAGER",7839,09/JUN/81,2450,,10
7788,"SCOTT","ANALYST",7566,09/DEC/82,3000,,20
7839,"KING","PRESIDENT",,17/NOV/81,5000,,10
7844,"TURNER","SALESMAN",7698,08/SEP/81,1500,,30
7876,"ADAMS","CLERK",7788,12/JAN/83,1100,,20
7900,"JAMES","CLERK",7698,03/DEC/81,950,,30
7902,"FORD","ANALYST",7566,03/DEC/81,3000,,20
7934,"MILLER","CLERK",7782,23/JAN/82,1300,,10
SQL> set serverout on
SQL> DECLARE
2 p_table VARCHAR2(200) := 'EMP2';
3 p_dir VARCHAR2(200) := 'TEMP';
4 p_filename VARCHAR2(200) := 'scott.csv';
5 p_ignore_headerlines integer := 1;
6 p_delimiter varchar2(2) := ',';
7 p_optional_enclosed varchar2(2) := '"';
8 v_return NUMBER;
9 BEGIN
10 v_return := LOAD_data(p_table => p_table
11 ,p_dir => p_dir
12 ,p_filename => p_filename
13 ,p_ignore_headerlines => p_ignore_headerlines
14 ,p_delimiter => p_delimiter
15 ,p_optional_enclosed => p_optional_enclosed);
16
17 dbms_output.put_line('v_return = ' || v_return);
18 END;
19 /
Inside begin...
Inside begin...
insert into EMP2(EMPNO,ENAME,JOB,MGR,HIREDATE,SAL,COMM,DEPTNO) values (:b1,:b2,:b3,:b4,to_date(:b5,"YYYY-MM-DD HH24:MI:SS"),:b6,:b7,:b8)
DECLARE
*
ERROR at line 1:
ORA-00984: column not allowed here
ORA-06512: at "SYS.DBMS_SQL", line 1134
ORA-06512: at "MCDONAC.LOAD_DATA", line 55
ORA-06512: at line 10
So I don't want double quotes there....I fix that
SQL> CREATE OR REPLACE FUNCTION LOAD_data ( p_table in varchar2,
2 p_dir in varchar2 DEFAULT 'TEMP' ,
3 P_FILENAME in varchar2,
4 p_ignore_headerlines IN INTEGER DEFAULT 1,
5 p_delimiter in varchar2 default ',',
6 p_optional_enclosed in varchar2 default '"' )
7 return number
8 is
9 l_input utl_file.file_type;
10 l_theCursor integer default dbms_sql.open_cursor;
11 l_lastLine varchar2(4000);
12 l_cnames varchar2(4000);
13 l_bindvars varchar2(4000);
14 l_status integer;
15 l_cnt number default 0;
16 l_rowCount number default 0;
17 l_sep char(1) default NULL;
18 L_ERRMSG varchar2(4000);
19 V_EOF BOOLEAN := false;
20
21 begin
22 dbms_output.put_line ('Inside begin...');
23 dbms_output.put_line ('Inside begin...');
24 l_cnt := 1;
25 for TAB_COLUMNS in (
26 select column_name, data_type from user_tab_columns where upper(table_name) = upper(p_table) order by column_id
27 ) loop
28 l_cnames := l_cnames || tab_columns.column_name || ',';
29 l_bindvars := l_bindvars || case when tab_columns.data_type in ('DATE', 'TIMESTAMP(6)') then 'to_date(:b' || l_cnt || ',''YYYY-MM-DD HH24:MI:SS''),' else ':b'|| l_cnt || ',' end;
30
31 l_cnt := l_cnt + 1;
32 end loop;
33 l_cnames := rtrim(l_cnames,',');
34 L_BINDVARS := RTRIM(L_BINDVARS,',');
35
36 L_INPUT := UTL_FILE.FOPEN( P_DIR, P_FILENAME, 'r' );
37 IF p_ignore_headerlines > 0
38 THEN
39 BEGIN
40 FOR i IN 1 .. p_ignore_headerlines
41 LOOP
42 UTL_FILE.get_line(l_input, l_lastLine);
43 END LOOP;
44 EXCEPTION
45 WHEN NO_DATA_FOUND
46 THEN
47 v_eof := TRUE;
48 end;
49 END IF;
50
51
52
53 if not v_eof then
54 dbms_output.put_line('insert into ' || p_table || '(' || l_cnames || ') values (' || l_bindvars || ')');
55 dbms_sql.parse( l_theCursor, 'insert into ' || p_table || '(' || l_cnames || ') values (' || l_bindvars || ')', dbms_sql.native );
56
57 loop
58 begin
59 utl_file.get_line( l_input, l_lastLine );
60 exception
61 when NO_DATA_FOUND then
62 exit;
63 end;
64
65 if length(l_lastLine) > 0 then
66 for i in 1 .. l_cnt-1
67 LOOP
68
69 dbms_sql.bind_variable( l_theCursor, ':b'||i,
70 rtrim(rtrim(ltrim(ltrim(
71 REGEXP_SUBSTR(l_lastline,'(^|,)("[^"]*"|[^",]*)',1,i),p_delimiter),p_optional_enclosed),p_delimiter),p_optional_enclosed));
72 end loop;
73 begin
74 l_status := dbms_sql.execute(l_theCursor);
75 l_rowCount := l_rowCount + 1;
76 end;
77 end if;
78 end loop;
79
80 dbms_sql.close_cursor(l_theCursor);
81 utl_file.fclose( l_input );
82 ---commit;
83 end if;
84
85 commit;
86 RETURN L_ROWCOUNT;
87 end LOAD_data;
88 /
Function created.
SQL>
SQL>
SQL> ---------------------------------------Anonymous block to run above function-----------------------------------------------
SQL> -- create table emp2 as select * from scott.emp where 1=0;
SQL>
SQL> set serverout on
SQL> DECLARE
2 p_table VARCHAR2(200) := 'EMP2';
3 p_dir VARCHAR2(200) := 'TEMP';
4 p_filename VARCHAR2(200) := 'scott.csv';
5 p_ignore_headerlines integer := 1;
6 p_delimiter varchar2(2) := ',';
7 p_optional_enclosed varchar2(2) := '"';
8 v_return NUMBER;
9 BEGIN
10 v_return := LOAD_data(p_table => p_table
11 ,p_dir => p_dir
12 ,p_filename => p_filename
13 ,p_ignore_headerlines => p_ignore_headerlines
14 ,p_delimiter => p_delimiter
15 ,p_optional_enclosed => p_optional_enclosed);
16
17 dbms_output.put_line('v_return = ' || v_return);
18 END;
19 /
Inside begin...
Inside begin...
insert into EMP2(EMPNO,ENAME,JOB,MGR,HIREDATE,SAL,COMM,DEPTNO) values (:b1,:b2,:b3,:b4,to_date(:b5,'YYYY-MM-DD
HH24:MI:SS'),:b6,:b7,:b8)
DECLARE
*
ERROR at line 1:
ORA-01858: a non-numeric character was found where a numeric was expected
ORA-06512: at "SYS.DBMS_SQL", line 1721
ORA-06512: at "MCDONAC.LOAD_DATA", line 74
ORA-06512: at line 10
so I'm closer now - because line 74 means I got to the insert. My format mask doesn't match the data. So I've changed YYYY-MM-DD HH24:MI:SS to DD/MON/YY and voila ...
SQL> set serverout on
SQL> DECLARE
2 p_table VARCHAR2(200) := 'EMP2';
3 p_dir VARCHAR2(200) := 'TEMP';
4 p_filename VARCHAR2(200) := 'scott.csv';
5 p_ignore_headerlines integer := 1;
6 p_delimiter varchar2(2) := ',';
7 p_optional_enclosed varchar2(2) := '"';
8 v_return NUMBER;
9 BEGIN
10 v_return := LOAD_data(p_table => p_table
11 ,p_dir => p_dir
12 ,p_filename => p_filename
13 ,p_ignore_headerlines => p_ignore_headerlines
14 ,p_delimiter => p_delimiter
15 ,p_optional_enclosed => p_optional_enclosed);
16
17 dbms_output.put_line('v_return = ' || v_return);
18 END;
19 /
Inside begin...
Inside begin...
insert into EMP2(EMPNO,ENAME,JOB,MGR,HIREDATE,SAL,COMM,DEPTNO) values
(:b1,:b2,:b3,:b4,to_date(:b5,'DD/MON/YYYY'),:b6,:b7,:b8)
v_return = 14
PL/SQL procedure successfully completed.
So the routine is valid....as long as
1) you have a CSV file
2) the physical and logical directory exists
3) you have the right permissions