Home » SQL & PL/SQL » SQL & PL/SQL » Error handling in PL\SQL
Error handling in PL\SQL [message #199319] Mon, 23 October 2006 08:46 Go to next message
Messages: 13
Registered: October 2006
Junior Member
I would like to know if there is a way to catch and handle exceptions in pl\sql, so that the exception thrown by RAISE_APPLICATION_ERROR is the only exception in the error stack.

For example, if I create a new procedure:
curr_sal NUMBER; 
SELECT sal INTO curr_sal FROM emp WHERE empno = emp_id; 

IF curr_sal IS NULL THEN 
raise_application_error(-20101,'Salary is missing'); 
UPDATE emp SET sal = curr_sal + amount WHERE empno = emp_id; 
END raise_salary; 

Procedure created;

Then I execute this procedure on an employee with a NULL salary:

EXEC raise_salary(7900,1000);

I now get the following error stack:
ERROR at line 1:
ORA-20101: Salary is missing
ORA-06512: at "USERNAME.RAISE_SALARY", line 7
ORA-06512: at line 1

So I would like to know if there is any way to receive an error stack the contains only 1 line:
ORA-20101: Salary is missing

Re: Error handling in PL\SQL [message #199338 is a reply to message #199319] Mon, 23 October 2006 12:00 Go to previous messageGo to next message
andrew again
Messages: 2577
Registered: March 2000
Senior Member

You can isolate the message and pass it out as a parameter or function return value but I wouldn't recommend that becuase PL/SQL won't propogate it up the calling stack.
Re: Error handling in PL\SQL [message #199339 is a reply to message #199319] Mon, 23 October 2006 12:08 Go to previous message
Messages: 20847
Registered: June 2005
Location: Croatia, Europe
Senior Member
Account Moderator
Your procedure will raise '20101' exception only if the employee exists in the table, but his salary is NULL. You miss (in my opinion) handler of the NO-DATA-FOUND exception. It will be raised whenever you pass, as an argument, ID of the employee that doesn't exist in the table.

However, you'll always get the whole error stack. I can't offer much (as I don't know the answer to your question), but there is an inappropriate way to "shorten" it using the DBMS_OUTPUT.PUT_LINE instead of RAISE_APPLICATION_ERROR. This will work in SQL*Plus, for example, but is - once again - NOT appropriate way to handle exceptions.
Previous Topic: Grouping Question
Next Topic: Using Count in a group function
Goto Forum:

Current Time: Mon Oct 24 22:44:55 CDT 2016

Total time taken to generate the page: 0.13973 seconds