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
yonieilon
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:
CREATE OR REPLACE PROCEDURE raise_salary (emp_id NUMBER, amount NUMBER) AS 
curr_sal NUMBER; 
BEGIN 
SELECT sal INTO curr_sal FROM emp WHERE empno = emp_id; 

IF curr_sal IS NULL THEN 
raise_application_error(-20101,'Salary is missing'); 
ELSE 
UPDATE emp SET sal = curr_sal + amount WHERE empno = emp_id; 
END IF; 
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

Thanks,
Yoni.
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
http://awads.net/wp/2006/08/01/little-known-way-to-get-the-error-message-in-plsql/
http://www.psoug.org/reference/exception_handling.html

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
Littlefoot
Messages: 20897
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: Wed Dec 07 12:23:23 CST 2016

Total time taken to generate the page: 0.16309 seconds