Home » SQL & PL/SQL » SQL & PL/SQL » create file using utl_file
create file using utl_file [message #37077] Wed, 16 January 2002 09:36 Go to next message
nigist
Messages: 5
Registered: January 2002
Junior Member
Hi I am trying to create a file using utl_file and when i run my procedure the 'other' exception is raise. Could you please look into my code and see what i am missing .thank you

CREATE OR REPLACE procedure test3(siteno number, trandt date) as
v_outfile_handle utl_file.file_type;
v_site_no number(4);
v_appt_dt date;
v_end_time number(4);
v_count number(4);
cursor clntflw1 is
select min(site_no),min(appt_dt),substr(lpad(to_char(end_time),4,0),1,2),
count(substr(lpad(to_char(end_time),4,0),1,2))
from tmx.appointment
where site_no = siteno and
appt_dt = trandt
and end_time > '0'
group by site_no,appt_dt,substr(lpad(to_char(end_time),4,0),1,2);
begin
v_outfile_handle := utl_file.fopen('C:nigist','test.txt','A');
open clntflw1;
loop
fetch clntflw1 into v_site_no,v_appt_dt,v_end_time,v_count;
exit when clntflw1%notfound;
utl_file.put_line (v_outfile_handle,v_site_no||v_appt_dt||v_end_time||v_count);
end loop;
utl_file.fclose(v_outfile_handle);
close clntflw1;
EXCEPTION

WHEN NO_DATA_FOUND THEN
DBMS_OUTPUT.PUT_LINE('no_data_found');
UTL_FILE.FCLOSE(v_outfile_handle);
WHEN UTL_FILE.INVALID_PATH THEN
DBMS_OUTPUT.PUT_LINE('UTL_FILE.INVALID_PATH');
UTL_FILE.FCLOSE(v_outfile_handle);
WHEN UTL_FILE.READ_ERROR THEN
DBMS_OUTPUT.PUT_LINE(' UTL_FILE.READ_ERROR');
UTL_FILE.FCLOSE(v_outfile_handle);
WHEN UTL_FILE.WRITE_ERROR THEN
DBMS_OUTPUT.PUT_LINE('UTL_FILE.WRITE_ERROR');
UTL_FILE.FCLOSE(v_outfile_handle);
WHEN OTHERS THEN
DBMS_OUTPUT.PUT_LINE('other stuff');
UTL_FILE.FCLOSE(v_outfile_handle);
end test3;
Re: create file using utl_file [message #37079 is a reply to message #37077] Wed, 16 January 2002 11:35 Go to previous message
Suresh Vemulapalli
Messages: 624
Registered: August 2000
Senior Member
change when others exception part like below and run..

WHEN OTHERS THEN
DBMS_OUTPUT.PUT_LINE(SQLERRM);
Previous Topic: How I get the Tables' source
Next Topic: Read same table during Update Trigger Error
Goto Forum:
  


Current Time: Tue Mar 31 11:21:43 CDT 2020