ORA-29284 [message #289715] |
Tue, 25 December 2007 20:54 |
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 #289722 is a reply to message #289715] |
Tue, 25 December 2007 22:03 |
|
Barbara Boehmer
Messages: 9100 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 #289778 is a reply to message #289760] |
Wed, 26 December 2007 02:19 |
|
Michel Cadot
Messages: 68722 Registered: March 2007 Location: Saint-Maur, France, https...
|
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
|
|
|