Read and Write using UTL_FILE
663981Nov 6 2008 — edited Nov 11 2008Hi,
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!