PL/SQL Error Handling

Oracle Database: PL/SQL Error Handling - RAISE_APPLICATION_ERROR()

This type of error handling prevents execution of code that will result in an error. Using RAISE_APPLICATION_ERROR(), custom error handling can be implemented. The recommended error numbers have to be between -20000 and -20999.

DECLARE
   i_val PLS_INTEGER := &i_val;
   v_val PLS_INTEGER;
   o_exit_status PLS_INTEGER;
BEGIN
   o_exit_status := 0;
   IF i_val = 0 THEN
   RAISE_APPLICATION_ERROR(-20001,
   'Divisor cannot be -> '||i_val);
   END IF;
   SELECT 1/i_val INTO v_val FROM DUAL;

   DBMS_OUTPUT.PUT_LINE('PL/SQL Executed Successfully '||
   TO_CHAR(sysdate,'mm/dd/yyyy hh24:mi:ss'));
EXCEPTION
WHEN OTHERS THEN
   o_exit_status := SQLCODE;
   DBMS_OUTPUT.PUT_LINE('Error ['||
   o_exit_status||'] Occurred During '||
   'Load -> '||TO_CHAR(sysdate,'mm/dd/yyyy hh24:mi:ss'));
   DBMS_OUTPUT.PUT_LINE(SUBSTR(SQLERRM, 1, 100));
END;
/

Oracle Database: PL/SQL Error Handling - User Defined Exception

DECLARE
   i_val PLS_INTEGER := &i_val;
   v_val PLS_INTEGER;
   o_exit_status PLS_INTEGER :=0;
   v_invalid EXCEPTION;
BEGIN
   IF i_val = 0 THEN
   RAISE v_invalid;
   END IF;

   SELECT 1/i_val INTO v_val FROM DUAL;

   DBMS_OUTPUT.PUT_LINE('PL/SQL Executed Successfully '||
   TO_CHAR(sysdate,'mm/dd/yyyy hh24:mi:ss'));

EXCEPTION
WHEN OTHERS THEN
   o_exit_status := SQLCODE;
   DBMS_OUTPUT.PUT_LINE('Error ['||
   o_exit_status||'] Occurred During '||
   'Load -> '||TO_CHAR(sysdate,'mm/dd/yyyy hh24:mi:ss'));
   DBMS_OUTPUT.PUT_LINE(SUBSTR(SQLERRM, 1, 100));
END;
/

Oracle Database: PL/SQL Error Handling - Exception

This type of error handling results in a graceful exit in the event of an error

DECLARE
   i_val PLS_INTEGER := 0;
   v_val PLS_INTEGER;
   o_exit_status PLS_INTEGER;
BEGIN
   o_exit_status := 0;
   SELECT 1/i_val INTO v_val FROM DUAL;
   DBMS_OUTPUT.PUT_LINE('PL/SQL Executed Successfully '||
   TO_CHAR(sysdate,'mm/dd/yyyy hh24:mi:ss'));
EXCEPTION
   WHEN ZERO_DIVIDE THEN
    o_exit_status := SQLCODE;
   DBMS_OUTPUT.PUT_LINE('Zero Divisor Error ['||
    o_exit_status||'] Occurred During '||
    'Load -> '||TO_CHAR(sysdate,'mm/dd/yyyy hh24:mi:ss'));
   DBMS_OUTPUT.PUT_LINE(SUBSTR(SQLERRM, 1, 100));
WHEN OTHERS THEN
   o_exit_status := SQLCODE;
   DBMS_OUTPUT.PUT_LINE('ERROR ['||
    o_exit_status||'] Occurred During '||
    'Load -> '||TO_CHAR(sysdate,'mm/dd/yyyy hh24:mi:ss'));
   DBMS_OUTPUT.PUT_LINE(SUBSTR(SQLERRM, 1, 100));
END;
/

PL/SQL Error Handling: Exception with RAISE

When the exception is handled with a RAISE, the code exits by raising error to the caller (script/application)

DECLARE
   i_val PLS_INTEGER := 0;
   v_val PLS_INTEGER;
   o_exit_status PLS_INTEGER;
BEGIN
   o_exit_status := 0;
   SELECT 1/i_val INTO v_val FROM DUAL;
   DBMS_OUTPUT.PUT_LINE('PL/SQL Executed Successfully '||
    TO_CHAR(sysdate,'mm/dd/yyyy hh24:mi:ss'));
EXCEPTION
WHEN ZERO_DIVIDE THEN
   o_exit_status := SQLCODE;
   DBMS_OUTPUT.PUT_LINE('Zero Divisor Error ['||
    o_exit_status||'] Occurred During '||
    'Load -> '||TO_CHAR(sysdate,'mm/dd/yyyy hh24:mi:ss'));
   DBMS_OUTPUT.PUT_LINE(SUBSTR(SQLERRM, 1, 100));
   RAISE;
WHEN OTHERS THEN
   o_exit_status := SQLCODE;
   DBMS_OUTPUT.PUT_LINE('ERROR ['||
    o_exit_status||'] Occurred During '||
    'Load -> '||TO_CHAR(sysdate,'mm/dd/yyyy hh24:mi:ss'));
   DBMS_OUTPUT.PUT_LINE(SUBSTR(SQLERRM, 1, 100));
   RAISE;
END;
/

PL/SQL Error Handling

The above examples show basic types of error handling. In application code, ETL processes, the recommended method is to log the load/error status by setting up code as autonomous transactions and automate the alert process in the form of email to support staff (or log help desk incident/ticket) about the error so that action can be taken immediately or based on severity of the transaction process.

Back

Oracle registered trademark of Oracle Corporation.

Last Revised On: November 01, 2013

72530