-
hi,
to my knowledge the error message returned by oracle is not correct.
am i correct.
In this example i hv not given the path, i hv not opened the file.
when am i suppose to get this error message????
"User-Defined Exception"
SQL> ed
Wrote file afiedt.buf
1 declare
2 s varchar2(10);
3 ftyp UTL_FILE.FILE_TYPE;
4 begin
5 s:='abc';
6 utl_file.put_line(ftyp,s);
7 exception when others then
8 dbms_output.put_line('err >>> '||sqlcode||sqlerrm);
9* end;
SQL> set serverout on
SQL> /
err >>> 1User-Defined Exception
-
it is correct the error message will give the
line no of the UTL_FILE package.
it says it is USER DEFINED, error
-
Oracle have used User-Defined exceptions in UTL_FILE rather than 'real' errors or RAISE_APPLICATION_ERROR style errors. If you remove your exception handler you get something like this:
declare
s varchar2(10);
ftyp UTL_FILE.FILE_TYPE;
begin
s:='abc';
utl_file.put_line(ftyp,s);
end;
/
*
ERROR at line 1:
ORA-06510: PL/SQL: unhandled user-defined exception
ORA-06512: at "SYS.UTL_FILE", line 301
ORA-06512: at line 6
This is effectively the same as:
DECLARE
e_my_exception EXCEPTION;
BEGIN
RAISE e_my_exception;
END;
/
ERROR at line 1:
ORA-06510: PL/SQL: unhandled user-defined exception
ORA-06512: at line 4
The only difference is that Oracle is reporting that the unhandled exception occurred within a procedure call with UTL_FILE.
Cheers!
-
-
The point I am trying to put across is:
Even if a package defines a user defined exception, the message should have been an appropriate one. In this specific case, since the file handle itself was not opened, a more appropriate error message wud have been "Invalid File Handle".
Have any of u come across a similar case occuring in any of the other supplied packages?
-
It's not an uncommon thing to do. Oracle has exposed the exceptions to you so you can trap them yourself and make the messages as specific as you want:
BEGIN
-- Do UTL_FILE stuff here.
EXCEPTION
WHEN Utl_File.INVALID_PATH THEN
-- Do something
WHEN Utl_File.INVALID_MODE THEN
-- Do something
WHEN Utl_File.INVALID_FILEHANDLE THEN
-- Do something
WHEN Utl_File.INVALID_OPERATION THEN
-- Do something
WHEN Utl_File.WRITE_ERROR THEN
-- Do something
WHEN Utl_File.INTERNAL_ERROR THEN
-- Do something
WHEN OTHERS THEN
-- Do something
END;
/
If you look in the Oracle Supplied Packages manual you will see this approach is used in almost all of them. The first page for each package lists all exposed procedures, functions and exceptions.
Are you having a specific problem or is it just that you didn't expect this?
Cheers
-
An exception is defined using:
ex_small_feet EXCEPTION;
Unless you link this exception to an existing error message using a pragma, how will the exception know what is a meaningful error message? If there isn't a suitable Oracle error what can you do?
Oracle are not expecting you to expose these types of errors to a user. They are expecting you to trap them using the exposed exceptions and decide what you want to say to the user.
I think you are confusing this approach with RAISE_APPLICATION_ERROR which is supplied with a user-defined error number and message.
Does that answer your question or have I missed the point?
-
hey tim,
thx for ur time.
to answer ur first question ('Are you having a specific problem or is it just that you didn't expect this?'), yes, i didnt expect this error msg for this scenario. in fact i was expecting a write error exception or invalid filehandle exception. in the procedure that had this problem, i am scanning thru a cursor and the file was opened within the cursor. in one of the cases, the cursor returned no rows and threw this exception. since i was also using user-defined exceptions, i thought the problem was elsewhere. took me some time to figure this out. :-)
and no, i am not confusing this with raise_application_error.
thx once again.
-
