Oracle: Handling exceptions from child stored procedures
In some existing oracke procedure code I’m maintaining, I needed to handle and report on the exceptions thrown by a child stored procedure inside a parent stored procedure.
The child could throw expected custom exceptions using “”, but could also throw unexpected system exceptions. I wanted to be able to report on them all. This snippet of code below solved all my issues:
-- NOTE: You cannot use SQLCODE or SQLERRM
-- directly in a SQL statement. Instead, you
-- must assign their values to local variables,
-- then use the variables in the SQL statement.
DECLARE
v_code NUMBER;
v_errm VARCHAR2(550);
BEGIN
CHILD_STORED_PROC(1, 2, 3);
EXCEPTION
WHEN OTHERS THEN
v_code := SQLCODE;
v_errm := SQLERRM;
DBMS_OUTPUT.PUT_LINE('Error code ' || v_code || ': ' || v_errm);
END;
This code was derived from Oracle.com
