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. |
74908