Determine Type of PL/SQL Exception

Question

In an OTHERS exception block I'd like to display the type of exception.

EXCEPTION
    WHEN OTHERS THEN 
        DBMS_OUTPUT.PUT_LINE( -- I want to display the type of exception here -- ); 
END;

Answer 1

I would prefer a robust exception block rather than a trivial EXCEPTION WHEN OTHERS THEN without any specific purpose. Always remember, you catch exception and then DO something about it. Else, you need to RE-RAISE it to give the handle back to the caller.

In your exception block, add the following :

DBMS_UTILITY. FORMAT_ERROR_STACK; DBMS_UTILITY.FORMAT_ERROR_BACKTRACE This will give you the error stack and the backtrace procedure will let you know the exactnline number of error. Since EXCEPTION takes the handle from the caller, thus the caller never comes to know about the error raised. Include RAISE statement to re-raise the error and give it back to the caller. Better have an error logging table to have the errors logged for future research.

And regarding when others, please read my article, http://lalitkumarb.wordpress.com/2014/05/02/when-others-then-null-a-bug/