OraFAQ Forum: SQL & PL/SQL » util_file package exception/error handling

Home » SQL & PL/SQL » SQL & PL/SQL » util_file package exception/error handling

Show: Today's Messages :: Polls :: Message Navigator
E-mail to friend

util_file package exception/error handling [message #4061]

Wed, 06 November 2002 03:56

cormac
Messages: 25
Registered: November 2002

Junior Member

Hi,
I'm investigating file output error that appears to stem from the util_file package.
If I run my pl/sql (quite simply a truncate on a table, a select into variables using cursors and then the follow up standard insert into the same table).
This runs fine.
However, the users require a .dat file format and this is where util_file comes in.
The following is used for the IO, but the dat file stops being populated early on. I know to test that the init.ora must be accessed and the instance bounced, but my Q is this:

There is no exception/error handling in the util_file, is the following okay to use? This util_file previously had no exceptions and its not tested.
Does raise_application_error have to be included ?
Also, finally, is it possible to identify the exact line that util_file fallsovers populating the .dat file? -- see below for code

f_name := to_char(sysdate,'DDMMYYYY')||'.dat';
f_handle := utl_file.fopen('directory',f_name,'W');
FOR output_curs IN output_rec LOOP
l_output := output_curs.dline;
utl_file.put(f_handle,l_output);
end loop;
utl_file.fclose(f_handle);
exception
when utl_file.invalid_mode then dbms_output.put_line
dbms_output.put_line('Procedure Has Failed With error -20002,'invalid_mode');

WHEN utl_file.invalid_operation then
dbms_output.put_line('Procedure Has Failed With error -20054, Invalid
Operation');

WHEN utl_file.invalid_path then
dbms_output.put_line('Procedure Has Failed With error -20052, Invalid Path');

when utl_file.invalid_filehandle then
dbms_output.put_line('Procedure Has Failed With error
-20002,'invalid_filehandle');

when utl_file.read_error then
dbms_output.put_line('Procedure Has Failed With error -20004,'read_error');

when utl_file.write_error then
dbms_output.put_line('Procedure Has Failed With error -20005,'write_error');

when utl_file.internal_error then
dbms_output.put_line('Procedure Has Failed With error -20006,'internal_error');

end;

Report message to a moderator

Re: util_file package exception/error handling [message #4063 is a reply to message #4061]

Wed, 06 November 2002 05:43

Mahesh Rajendran
Messages: 10707
Registered: March 2002
Location: oracleDocoVille

Senior Member
Account Moderator

in most of the cases, this will be enough...

EXCEPTION
        WHEN NO_DATA_FOUND THEN
		DBMS_OUTPUT.PUT_LINE('no_data_found');
	WHEN UTL_FILE.INVALID_PATH THEN
		DBMS_OUTPUT.PUT_LINE('UTL_FILE.INVALID_PATH');
	WHEN UTL_FILE.WRITE_ERROR THEN
                DBMS_OUTPUT.PUT_LINE('UTL_FILE.WRITE_ERROR');
      	WHEN OTHERS THEN
                DBMS_OUTPUT.PUT_LINE('other stuff');

Report message to a moderator

Re: util_file package exception/error handling [message #4067 is a reply to message #4061]

Wed, 06 November 2002 07:16

cormac
Messages: 25
Registered: November 2002

Junior Member

Manjesh,
thanks for that.
I'll give it a whirl and let you know.
cheers
Cormac

Report message to a moderator

Previous Topic:	util_file package on 8i and case sensitivity
Next Topic:	inserting sysdate with time stamp in oracle 8i- URGENT

Goto Forum:

-=] Back to Top [=-

[ Syndicate this forum (XML) ] [

]

Current Time: Wed May 08 04:19:30 CDT 2024