Home » SQL & PL/SQL » SQL & PL/SQL » ORA-29284
ORA-29284 [message #289715] Tue, 25 December 2007 20:54 Go to next message
bowencheng
Messages: 2
Registered: December 2007
Junior Member
Hi ,

I used below PL/SQL to read file.The file contains over 40000 words in a line(it's a kind of EDIFact File).Therefore I used variable which belong to "long" to store the information.And I am sure that the file is exists and readable.But I always got ORA-29284: file read error.
I also tested another files which contains few characters a line.Below PL/SQL will works.
Pls kindly help to give solution.

thanks & best regards

declare
  out_file       utl_file.file_type ;
  lv_data        long ;
begin
  dbms_output.put_line('step1') ;
  out_file := utl_file.fopen('/export/home/wpsdata/casp','recv4.edi','r');
  dbms_output.put_line('step2') ;
  begin
    utl_file.get_line(out_file,lv_data);
  exception
    when others then
      utl_file.fclose_all ;
      dbms_output.put_line('lv_data:'||lv_data) ;      
      dbms_output.put_line('step3:'||sqlcode||':'||sqlerrm) ;      
  end ;
  utl_file.fclose_all ;
  dbms_output.put_line('end') ;
exception
  when others then
    utl_file.fclose_all ;
    dbms_output.put_line('step4:'||sqlcode||':'||sqlerrm) ;      
end ;





[code tags added by moderator; next time please add them yourself]

[Updated on: Tue, 25 December 2007 21:56] by Moderator

Report message to a moderator

Re: ORA-29284 [message #289719 is a reply to message #289715] Tue, 25 December 2007 21:46 Go to previous messageGo to next message
BlackSwan
Messages: 25036
Registered: January 2009
Location: SoCal
Senior Member
<comdb04-oracle@stagedb02.hitbox.com:/a/oracle:> oerr ora 29284
29284, 00000, "file read error"
// *Cause:  An attempt to read from a file failed.
// *Action: Verify that the file exists, and that it is accessible, and 
//          that it is open in read mode.
Re: ORA-29284 [message #289721 is a reply to message #289715] Tue, 25 December 2007 21:52 Go to previous messageGo to next message
Barbara Boehmer
Messages: 8625
Registered: November 2002
Location: California, USA
Senior Member
The default size for the second parameter in utl_file.get_line is 1000 bytes. You can increase it to the maximum of 32767 by specifying that as the fourth parameter for utl_file.fopen:

out_file := utl_file.fopen('/export/home/wpsdata/casp','recv4.edi','r', 32767);

If your file is bigger than that, then you will need to parse and process one substring at a time. Also you might want to use CLOB instead of LONG.


Re: ORA-29284 [message #289722 is a reply to message #289715] Tue, 25 December 2007 22:03 Go to previous messageGo to next message
Barbara Boehmer
Messages: 8625
Registered: November 2002
Location: California, USA
Senior Member
The following demonstrates the problem by using the same file with different fourth parameter sizes for utl_file.fopen. The first one uses a very small number that reproduces the error and the second one corrects the problem by increasing the number. I used an 8-character file, so 5 was too small, but 10 was big enough.


-- reproduction of error:
SCOTT@orcl_11g> declare
  2    out_file       utl_file.file_type ;
  3    lv_data	      long ;
  4  begin
  5    dbms_output.put_line('step1') ;
  6    out_file := utl_file.fopen('ORACLE11G','test.dat','r', 5);
  7    dbms_output.put_line('step2') ;
  8    begin
  9  	 utl_file.get_line(out_file,lv_data);
 10    exception
 11  	 when others then
 12  	   utl_file.fclose_all ;
 13  	   dbms_output.put_line('lv_data:'||lv_data) ;
 14  	   dbms_output.put_line('step3:'||sqlcode||':'||sqlerrm) ;
 15    end ;
 16    utl_file.fclose_all ;
 17    dbms_output.put_line('end') ;
 18  exception
 19    when others then
 20  	 utl_file.fclose_all ;
 21  	 dbms_output.put_line('step4:'||sqlcode||':'||sqlerrm) ;
 22  end;
 23  /
step1
step2
lv_data:
step3:-29284:ORA-29284: file read error
end

PL/SQL procedure successfully completed.


-- correction of problem by increasing utl_file.fopen fourth parameter:
SCOTT@orcl_11g> declare
  2    out_file       utl_file.file_type ;
  3    lv_data	      long ;
  4  begin
  5    dbms_output.put_line('step1') ;
  6    out_file := utl_file.fopen('ORACLE11G','test.dat','r', 10);
  7    dbms_output.put_line('step2') ;
  8    begin
  9  	 utl_file.get_line(out_file,lv_data);
 10    exception
 11  	 when others then
 12  	   utl_file.fclose_all ;
 13  	   dbms_output.put_line('lv_data:'||lv_data) ;
 14  	   dbms_output.put_line('step3:'||sqlcode||':'||sqlerrm) ;
 15    end ;
 16    utl_file.fclose_all ;
 17    dbms_output.put_line('end') ;
 18  exception
 19    when others then
 20  	 utl_file.fclose_all ;
 21  	 dbms_output.put_line('step4:'||sqlcode||':'||sqlerrm) ;
 22  end;
 23  /
step1
step2
end

PL/SQL procedure successfully completed.

SCOTT@orcl_11g> 

Re: ORA-29284 [message #289750 is a reply to message #289722] Wed, 26 December 2007 00:43 Go to previous messageGo to next message
bowencheng
Messages: 2
Registered: December 2007
Junior Member
Dears ,

My edi file is over 40000 characters a line(pls ref attached file).I also found get_line only allow 32767 bytes a line.
Therefore,I still can't read the file.

best regards
  • Attachment: RECV.EDI
    (Size: 41.07KB, Downloaded 305 times)
Re: ORA-29284 [message #289759 is a reply to message #289750] Wed, 26 December 2007 01:13 Go to previous messageGo to next message
Barbara Boehmer
Messages: 8625
Registered: November 2002
Location: California, USA
Senior Member
I guess you can't use utl_file. How about using dbms_lob.loadclobfromfile to load it into a clob, then parse it from there?
Re: ORA-29284 [message #289760 is a reply to message #289750] Wed, 26 December 2007 01:17 Go to previous messageGo to next message
Michel Cadot
Messages: 64103
Registered: March 2007
Location: Nanterre, France, http://...
Senior Member
Account Moderator
In this case, you can't use utl_file and have to create your own package using Java class and LOB for instance or an external program.

Regards
Michel
Re: ORA-29284 [message #289778 is a reply to message #289760] Wed, 26 December 2007 02:19 Go to previous message
Michel Cadot
Messages: 64103
Registered: March 2007
Location: Nanterre, France, http://...
Senior Member
Account Moderator
Reported message

Reported By: bowencheng On: Wed, 26 December 2007 09:16 In: SQL & PL/SQL PL/SQL Experts ORA-29284
Reason: Dears , I agree that utl_file is not works for this kind of file. I will use external program to hanle. thanks & best regards

(Wrong button pressed I think)

[Updated on: Wed, 26 December 2007 02:20]

Report message to a moderator

Previous Topic: utl_file
Next Topic: Problem in doing group by in query
Goto Forum:
  


Current Time: Sun Dec 04 08:53:19 CST 2016

Total time taken to generate the page: 0.14528 seconds