CODEWITHSUNDEEP
oracle-databasestored-proceduresplsql
Mohan
Mohan

Reputation: 473

Exception handling in Procedure with nested functions in pl/sql

I have an pl/sql procedure as mentioned below

CREATE OR REPLACE PROCEDURE add_affectedCircle
(v_affected_circle IN v_circle.circle_code%type)
IS
    v_circle_id  NUMBER;

BEGIN
        v_circle_id := get_circleID(v_affected_circle);
        INSERT INTO vf_affected_circle (affected_circle) 
        values (v_circle_id);

EXCEPTION
WHEN OTHERS THEN
RAISE_APPLICATION_ERROR (-20101, 'Problem in loading Affected Circle data');
END;
/

First I was checking the particular data v_affected_circle is available in circle_code column in vf_circle table using function get_circleID and returning the circle_id associated with circle_code.

CREATE OR REPLACE FUNCTION get_circleID 
( v_circle_code  vf_circle.circle_code%TYPE)
RETURN vf_circle.circle_id%TYPE
IS
    return_value  vf_circle.circle_id%TYPE; 
BEGIN
    BEGIN
        SELECT circle_id INTO return_value
        FROM vf_circle
        WHERE circle_code = v_circle_code;
   END;
   RETURN return_value;
EXCEPTION
WHEN NO_DATA_FOUND THEN
RAISE_APPLICATION_ERROR(-20102, 'Circle Code is wrong or not available');
END get_circleID;
/

I am tying to raise user exception (-20102, 'Circle Code is wrong or not same as records') in the function get_circleID when the data is not available for the provided data v_affected_circle. This function is called from procedure add_affectedCircle

I am trying to raise another user exception if some problem happens in loading data into particular table vf_affected_circle as (-20102, 'Problem in loading Affected Circle data') .

Now the problem is I try to run the procedure add_affectedCircle with wrong values, I am getting exception only from procedure not from the function. Also I am seeing other exceptions which I am unable to handle and suppress it.

Now I am getting exception like this 

BEGIN add_affectedCircle(69,'ODI:ASA'); END;

*
ERROR at line 1:
ORA-20102: Problem in loading Affected Circle data
ORA-06512: at "SRUSER.ADD_AFFECTEDCIRCLE", line 28
ORA-06512: at line 1

But what I need to show is like this

   ORA-20102: Circle Code is wrong or not available
   ORA-20101: Problem in loading Affected Circle data

How can I achieve this?

Upvotes: 0

Views: 2910

Answers (1)

Alex Poole
Alex Poole

Reputation: 191235

The RAISE_APPLICATION_ERROR procedure has a third parameter which controls whether the exception replaces the current exception stack (the default behaviour) or adds to it.

If you specify TRUE, PL/SQL puts error_code on top of the error stack. Otherwise, PL/SQL replaces the error stack with error_code.

So if you pass it TRUE it will show both messages; in the call from your procedure:

...
EXCEPTION
WHEN OTHERS THEN
RAISE_APPLICATION_ERROR (-20101, 'Problem in loading Affected Circle data',
  TRUE);
END;

gives you the output:

BEGIN
*
ERROR at line 1:
ORA-20101: Problem in loading Affected Circle data
ORA-06512: at "SRUSER.ADD_AFFECTEDCIRCLE", line 13
ORA-20102: Circle Code is wrong or not available
ORA-06512: at line 2

To only show the messages and not the other stack information you'd need to manipulate the stack. This article touches on how to use format_error_backtrace to extract the information you're interested in, but unfortunately the link to the BT package are dead. Some of the same ground is covered here. Essentially you need to parse the exception string into lines, and only display those that start with ORA-20%, I think.

But be careful about losing information that might actually be vital. In particular I'd suggest you only trap the specific errors you're looking for, not OTHERS - leave that alone so you can deal with unexpected errors and don't hide something important.

Upvotes: 5

Related Questions