Skip to Main Content

Breadcrumb

May 4th

Question and Answer

Connor McDonald

Thanks for the question, Ankit.

Asked: May 25, 2017 - 5:19 am UTC

Last updated: February 14, 2018 - 2:16 am UTC

Version: 11.2

Viewed 10K+ times! This question is

You Asked

Hello Experts,

I have below pl/sql function with that I am trying to upload the data from .xls file to pl/sql table but getting ora-29284 file read error.

PL/SQL Function:

CREATE OR REPLACE FUNCTION LOAD_data ( 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;
 
 begin
 dbms_output.put_line ('Inside begin...');
 dbms_output.put_line ('Inside begin...');
 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 || ',"YYYY-MM-DD 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' );
 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 ,systimestamp );
 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 );
 
 commit;
 RETURN L_ROWCOUNT;
 end LOAD_data;

---------------------------------------Anonymous block to run above function-----------------------------------------------
DECLARE
 p_table VARCHAR2(200) := 'temp_data';
 p_dir VARCHAR2(200) := 'ORA_DIR';
 p_filename VARCHAR2(200) := 'Data1.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;


Please suggest.

Thanks,
Ankit.


and Connor said...

Your approach seems to be assuming input data as csv.

But an XLS file is not CSV, its a binary format. You'll need to save your XLS file as a CSV and then load it.

Rating

  (2 ratings)

Is this answer out of date? If it is, please let us know via a Comment

Comments

Ankit, May 26, 2017 - 3:20 am UTC

Thanks a lot.

Using same script for CSV

Gurpreet, February 13, 2018 - 2:17 pm UTC

Hi ,

I am using same script for .csv file, but still getting the file read error.

Can you please suggest the possible case? It would be really great.

Regards,
Gurpreet
Connor McDonald
February 14, 2018 - 2:16 am UTC

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


More to Explore

PL/SQL demos

Check out more PL/SQL tutorials on our LiveSQL tool.

PL/SQL docs

PL/SQL reference manual from the Oracle documentation library