Skip to Main Content

SQL & PL/SQL

Announcement

For appeals, questions and feedback about Oracle Forums, please email oracle-forums-moderators_us@oracle.com. Technical questions should be asked in the appropriate category. Thank you!

Read and Write using UTL_FILE

663981Nov 6 2008 — edited Nov 11 2008
Hi,
I have a problem with a procedure i have written... in this procedure i have to read form a text file compare it with data in table and the rows that sastify the conditions have to be writen ito a new text file

CODE
----------
declare
v_path varchar2(50) := '/dev/iodata';
v_filename varchar2(50) := 'Text1.txt';
p_dir varchar2(50) := '/dev/iodata';
p_filename varchar2(50) := 'Text2.txt';
v_input_file utl_file.file_type;
p_output_file utl_file.file_type;
cursor C1 is select id, value, chem from Org;
v_input_buffer varchar2(500);
p_output_buffer varchar2(500);
v_id Varchar2(20);

begin
v_input_file := utl_file.fopen (v_path, v_filename, 'R');

p_output_file := utl_file.fopen( p_dir, p_filename, 'w' );

Loop

utl_file.get_line (v_input_file, v_input_buffer);

v_id := LTRIM(RTRIM(substr( v_input_buffer, 1, 12 )));
DBMS_OUTPUT.PUT_LINE(v_input_buffer);

for i in C1

loop

if i.id=v_id

then
p_output_buffer:= i.id || ' ' || i.value|| ' ' ||i.chem ;
DBMS_OUTPUT.PUT_LINE(p_output_buffer);

utl_file.put_line(p_output_file,p_output_buffer);
DBMS_OUTPUT.PUT_LINE('******');
end if;

end loop;

utl_file.fclose(p_output_file);

End Loop;

exception
when no_data_found then
utl_file.fclose(v_input_file);

end ;

** all the DBMS_OUTPUT_LINE are used for debug purpose

OUTPUT
-------------------
00002010502 1
00002010502 1
******
00002010504 1
00002010504 1
declare
*
ERROR at line 1:
ORA-29282: invalid file ID
ORA-06512: at "SYS.UTL_FILE", line 774
ORA-06512: at line 35

Text1.txt have 2 lines as you can see v_input_buffer reads it i.e the file is opening and also reading
The for loop is comparing the v_id to i.id and also writing to Text2.txt when i check the file its writing
BUT when it trys writing second line i am getting eroor...... its not writing and throwing error

I have added all the exceptions of UTIL_FILE to my code and found that the exception as
ERROR at line 1:
ORA-20053: Invalid Filehandle
and now i am not able to figure out how to handle this error.... Please let me know how to slove this error....

Thanks!
This post has been answered by JustinCave on Nov 7 2008
Jump to Answer
Comments
Locked Post
New comments cannot be posted to this locked post.
Post Details
Locked on Dec 9 2008
Added on Nov 6 2008
40 comments
6,362 views