Home » SQL & PL/SQL » SQL & PL/SQL » Oracle UTL_FILE exception read_error
Oracle UTL_FILE exception read_error [message #297232] Wed, 30 January 2008 14:11 Go to next message
ashishmate
Messages: 90
Registered: February 2005
Location: Mumbai
Member

I am Trying to read CSV file from oracle using UTL_FILE.
It reads almost all file and working fine but there in one file which oracle not able to read. when ever i submit this file for reading it fires exception utl_file.read_error. i search this on net and got to know that this is
Quote:
The operating system returned an error when you tried to read from the file. (This does not occur very often.)
.
i want to know why i am getting this error for particular file. checked the file there are no junk characters in that. file size is normal. and permeation is fine. my oracle(9i) is on unix server.

please let me know cause for this exception and how to get read of this

declare
  input_file   utl_file.file_type;
  input_buffer varchar2(4000);
begin


dbms_output.put_line('start');
  input_file := utl_file.fopen ('/data/download/','mdfkljdfkldfjkl.csv', 'R');

IF NOT(utl_file.is_open(input_file)) THEN


  utl_file.get_line (input_file, input_buffer);
  dbms_output.put_line(input_buffer);
  utl_file.get_line (input_file, input_buffer);
  dbms_output.put_line(input_buffer);
  utl_file.fclose(input_file);

end if;
  dbms_output.put_line('done');
  exception
            when utl_file.invalid_path then
               dbms_output.put_line('-1:(DUMMY.DK_EXR_CSV_LOAD_INVALID_PATH)');
            when utl_file.internal_error then
               dbms_output.put_line('-1:(DUMMY.DK_EXR_CSV_LOAD_INTERNAL_ERROR)');
            when utl_file.read_error then
                dbms_output.put_line('-1:(DUMMY.DK_EXR_CSV_LOAD_READ_ERROR)');
            when utl_file.invalid_filehandle then
               dbms_output.put_line('-1:(DUMMY.DK_EXR_CSV_LOAD_INVALID_FILE)');
            when utl_file.invalid_operation then
               dbms_output.put_line('-1:(DUMMY.DK_EXR_CSV_LOAD_INVALID_OPER)');
           when no_data_found THEN
           dbms_output.put_line('End of file reached; closing input file  || s_filename)');
             when others then
                dbms_output.put_line('-1:(DUMMY.DK_EXR_CSV_LOAD_OTHER_ERROR)' || sqlcode || ':' || sqlerrm);
end;




Thanks In Advance Smile
ASHish....
Re: Oracle UTL_FILE exception read_error [message #297243 is a reply to message #297232] Wed, 30 January 2008 15:19 Go to previous messageGo to next message
S.Rajaram
Messages: 1027
Registered: October 2006
Location: United Kingdom
Senior Member

SQL> select * from v$version;

BANNER
----------------------------------------------------------------
Oracle Database 10g Enterprise Edition Release 10.1.0.2.0 - Prod
PL/SQL Release 10.1.0.2.0 - Production
CORE    10.1.0.2.0      Production
TNS for 32-bit Windows: Version 10.1.0.2.0 - Production
NLSRTL Version 10.1.0.2.0 - Production

SQL> 
  1  declare
  2  file_handle utl_file.file_type;
  3  output_line varchar2(32767);
  4  i number;
  5  begin
  6  i := 0;
  7  file_handle := utl_file.fopen('TEST_DIR','test.txt','r');
  8  loop
  9     begin
 10        utl_file.get_line(file_handle,output_line);
 11        dbms_output.put_line('Line Number : ' || i || ' and length is : ' || length(output_line));
 12        i := i + 1;
 13     exception
 14     when no_data_found
 15     then
 16         exit;
 17     end;
 18  end loop;
 19  if utl_file.is_open(file_handle)
 20  then
 21     utl_file.fclose(file_handle);
 22  end if;
 23* end;
SQL> /
Line Number : 0 and length is : 54
Line Number : 1 and length is :
Line Number : 2 and length is : 80
Line Number : 3 and length is : 80
Line Number : 4 and length is : 80
Line Number : 5 and length is : 80
Line Number : 6 and length is : 80
Line Number : 7 and length is : 80
Line Number : 8 and length is : 80
Line Number : 9 and length is : 80
Line Number : 10 and length is : 80
Line Number : 11 and length is : 80
Line Number : 12 and length is : 80
Line Number : 13 and length is : 80
Line Number : 14 and length is : 80
Line Number : 15 and length is :
Line Number : 16 and length is : 80
Line Number : 17 and length is : 80
Line Number : 18 and length is : 80
Line Number : 19 and length is : 80
Line Number : 20 and length is : 80
Line Number : 21 and length is :
Line Number : 22 and length is : 19
Line Number : 23 and length is : 6
Line Number : 24 and length is :
declare
*
ERROR at line 1:
ORA-29284: file read error
ORA-06512: at "SYS.UTL_FILE", line 98
ORA-06512: at "SYS.UTL_FILE", line 656
ORA-06512: at line 10



  1  declare
  2  file_handle utl_file.file_type;
  3  output_line varchar2(32767);
  4  i number;
  5  begin
  6  i := 0;
  7  file_handle := utl_file.fopen('TEST_DIR','test.txt','r', 32767);
  8  loop
  9     begin
 10        utl_file.get_line(file_handle,output_line);
 11        dbms_output.put_line('Line Number : ' || i || ' and length is : ' || length(output_line));
 12        i := i + 1;
 13     exception
 14     when no_data_found
 15     then
 16         exit;
 17     end;
 18  end loop;
 19  if utl_file.is_open(file_handle)
 20  then
 21     utl_file.fclose(file_handle);
 22  end if;
 23* end;
SQL> /
Line Number : 0 and length is : 54
Line Number : 1 and length is :
Line Number : 2 and length is : 80
Line Number : 3 and length is : 80
Line Number : 4 and length is : 80
Line Number : 5 and length is : 80
Line Number : 6 and length is : 80
Line Number : 7 and length is : 80
Line Number : 8 and length is : 80
Line Number : 9 and length is : 80
Line Number : 10 and length is : 80
Line Number : 11 and length is : 80
Line Number : 12 and length is : 80
Line Number : 13 and length is : 80
Line Number : 14 and length is : 80
Line Number : 15 and length is :
Line Number : 16 and length is : 80
Line Number : 17 and length is : 80
Line Number : 18 and length is : 80
Line Number : 19 and length is : 80
Line Number : 20 and length is : 80
Line Number : 21 and length is :
Line Number : 22 and length is : 19
Line Number : 23 and length is : 6
Line Number : 24 and length is :
Line Number : 25 and length is : 2000
Line Number : 26 and length is : 2000
Line Number : 27 and length is : 2000
Line Number : 28 and length is :
Line Number : 29 and length is : 57
Line Number : 30 and length is :
Line Number : 31 and length is : 2000
Line Number : 32 and length is : 2000
Line Number : 33 and length is : 2000
Line Number : 34 and length is :
Line Number : 35 and length is : 14

PL/SQL procedure successfully completed.


From next time could you please do the same like what I did.

Thanks

Raj
Re: Oracle UTL_FILE exception read_error [message #297247 is a reply to message #297243] Wed, 30 January 2008 16:20 Go to previous messageGo to next message
ashishmate
Messages: 90
Registered: February 2005
Location: Mumbai
Member

Thanks..... I will keep this in mind....
but i am not able to find out why that exception occurs. can you please explain.

one more thing i am able to utl_file.fopen bt getting error while trying to read the file i.e utl_file.get_line(input_file, input_buffer);



Thanks..
ASHish....

[Updated on: Thu, 31 January 2008 03:07]

Report message to a moderator

Re: Oracle UTL_FILE exception read_error [message #297572 is a reply to message #297247] Fri, 01 February 2008 07:06 Go to previous messageGo to next message
S.Rajaram
Messages: 1027
Registered: October 2006
Location: United Kingdom
Senior Member
before version
file_handle := utl_file.fopen('TEST_DIR','test.txt','r');
After version
file_handle := utl_file.fopen('TEST_DIR','test.txt','r', 32767);

Check the last parameter while opening a file. Default value is 1024 (i.e) it means You are telling oracle that I am not expecting more than 1024 characters in a line. I have changed it to maximum allowed 32767.

HTH

Regards

Raj

P.S : Spelling mistake corrected

[Updated on: Fri, 01 February 2008 07:07]

Report message to a moderator

Re: Oracle UTL_FILE exception read_error [message #298252 is a reply to message #297243] Tue, 05 February 2008 08:17 Go to previous messageGo to next message
ashishmate
Messages: 90
Registered: February 2005
Location: Mumbai
Member

Thanks lot Raj....its working fine now.
one more thing will this change affect the performance????
ASHish....
Re: Oracle UTL_FILE exception read_error [message #298258 is a reply to message #298252] Tue, 05 February 2008 08:25 Go to previous messageGo to next message
S.Rajaram
Messages: 1027
Registered: October 2006
Location: United Kingdom
Senior Member
Benchmark it. As far as i know it should not have any impact on performance. But as "Tom says" don't go by theory, test it and come to conclusion. I leave that exercise to you.

BTW, Thanks for your feedback.

Good luck

Cheers

Raj

[Updated on: Tue, 05 February 2008 08:25]

Report message to a moderator

Re: Oracle UTL_FILE exception read_error [message #307668 is a reply to message #298258] Wed, 19 March 2008 07:00 Go to previous message
ashishmate
Messages: 90
Registered: February 2005
Location: Mumbai
Member

i have tried it with 200 test records did't find any noticeable deference. seems like not affecting performance much....

Thanks once again...
Previous Topic: insert
Next Topic: copy table from 1 database to another
Goto Forum:
  


Current Time: Thu Dec 08 18:31:39 CST 2016

Total time taken to generate the page: 0.15281 seconds