Tracing Errors

For most of us, writing procedures, functions or say just a PL/SQL Code Block based on a given logic is wasy. The challenge lies in designing and placing the "Exception Handling" Block with the intent of capturing the correct line of Code that raised the exception so that one can them Zoom into the erroneous line and do make the necessary fixes. Now many of you would agree that this comes with experience 'n' ofcourse proactive thinking.

Now conder the following Code Snippet,

WHEN NO_DATA_FOUND

THEN dbms_output.put_line (sqlerrm);

END;

Looks familiar...? But here's a catch - this code block will keep rescuing us as far as Error String is less than 255 Characters. (The limitation with put_line() is that it can capture a max of 255 Characters).

Oracle has revolved this by its offering -DBMS_UTILITY.FORMAT_ERROR_STACK

BEGIN

error();

EXCEPTION WHEN OTHERS THEN

DBMS_OUTPUT.PUT_LINE( SQLERRM );

DBMS_OUTPUT.PUT_LINE( DBMS_UTILITY.FORMAT_ERROR_BACKTRACE );

END;

/

ORA-00900: invalid SQL statement

ORA-06512: at line 2

Now, as evident from output above, the information provided in error stack is sufficent for application error logs and can be worked upon accordingly. And the best part is there's no limitation of number of characters we can capture in error stack. Makes life easy indeed :)