| User defined Exceptions - Error [message #42769] |
Thu, 08 May 2003 06:49  |
Jeslie
Messages: 26
Registered: April 2003
|
Junior Member |
|
|
Hello
I have problem in using user exceptions. you can run this code in scott/tiger.............. I get few errors eventhough its handeled by user defined exceptions
-----------------------------------------------------
1 declare
2 userException Exception;
3 v_name emp.ename%type;
4 v_err varchar2(200);
5 begin
6 ----------------- statement
7 begin
8 select ename
9 into v_name
10 from emp
11 where empno=1;
12 exception
13 when no_data_found then
14 v_err :='No data found for the employee';
15 raise userException;
16 when others then
17 v_err :='Others Exception';
18 raise userException;
19 end;
20 exception
21 when userException then
22 raise_application_error(-20901,v_err,TRUE);
23* end;
24 /
declare
*
ERROR at line 1:
ORA-20901: No data found for the employee
ORA-06512: at line 22
ORA-06510: PL/SQL: unhandled user-defined exception
ORA-01403: no data found
-----------------------------------------------------
why do this error unhandled user-defined exception and not data found occurs eventhough the error is handeled by userException
if i remove the parameter TRUE from raise_application_error, i dont get these error
can you explain the reason and how to avoid the last two errors.
Thanks
|
|
|
|
|
|
| Re: User defined Exceptions - Error [message #42772 is a reply to message #42771] |
Thu, 08 May 2003 07:27  |
Jeslie
Messages: 26
Registered: April 2003
|
Junior Member |
|
|
Thanks for your responce
I understand that no data found error can not be avoided, but why do i get Unhandled userdefined exception error( I've handeled the user Exception)
can you please explain
|
|
|
|
| Re: User defined Exceptions - Error [message #42774 is a reply to message #42771] |
Thu, 08 May 2003 07:37 |
Jeslie
Messages: 26
Registered: April 2003
|
Junior Member |
|
|
Thanks for your responce
I understand that no data found error can not be avoided, but why do i get Unhandled userdefined exception error( I've handeled the user Exception)
can you please explain
|
|
|
|
| Re: User defined Exceptions - Error [message #42791 is a reply to message #42772] |
Fri, 09 May 2003 06:37 |
 |
Barbara Boehmer
Messages: 9106
Registered: November 2002
Location: California, USA
|
Senior Member |
|
|
Look at your error stack from bottom to top, when analyzing how Oracle processes the exceptions. The main point that seems to be causing you confusion is that it is regarded as an unhandled user-defined exception in the inner block, until it is passed to the outer block, where it is then handled.
ORA-01403: When the query in your inner block does not return any rows, that raises the no_data_found Oracle exception, which causes it to go to the exception section of your inner block. There it finds that when no data is found, it is to raise the user-defined exception userException, which is not handled within the inner block.
ORA-06510: Oracle passes that unhandled user-defined exception userException from the inner block to the outer block, which causes it to go to the exception section in the outer block. It is unhandled at this point, because the inner block does not tell Oracle what to do with it.
ORA-06512: In the exception section of the outer block, at line 22 of your code, Oracle finds out how to handle your user-defined exception userException. It is to raise application error ORA-20901. Now the user-defined exception is handled.
ORA-20901: The raise_application_error is passed to the calling environment (SQL*Plus).
|
|
|
|
] 
Blog
Search
Help
Members
Register
Login
Home
