SQLCODE and SQLERRM in EXCEPTION

In PL/SQL, you can combine both SQLCODE and SQLERRM within an EXCEPTION block to provide detailed information about the error. Here’s how you might do it:

Example:


BEGIN
    -- Code that may cause an error, such as dividing by zero
    DECLARE
        v_num NUMBER := 1;
        v_den NUMBER := 0;
        v_result NUMBER;
    BEGIN
        v_result := v_num / v_den; -- This will cause a division by zero error
    EXCEPTION
        WHEN OTHERS THEN
            DBMS_OUTPUT.PUT_LINE('Error Code: ' || SQLCODE);
            DBMS_OUTPUT.PUT_LINE('Error Message: ' || SQLERRM);
    END;
END;
/

Output:

Error Code: -1476
Error Message: ORA-01476: divisor is equal to zero

Explanation:

SQLCODE returns -1476, the Oracle error code for a division-by-zero operation.

SQLERRM provides a descriptive message: ORA-01476: divisor is equal to zero.

This approach allows you to handle errors comprehensively, logging both the specific error code and a detailed error message, which can be useful for debugging and maintaining your PL/SQL programs.

DEV Community

SQLCODE and SQLERRM in EXCEPTION