Tracing the error in a Stored Procedure/Function

In one of our previous blog post, we covered the format_error_stack procedure of the dbms_utility package to display errors in lieu of using SQLERRM. In this blog post we will continue with the dbms_utility package to see how one of its subprograms (procedure) can help us track back the error to its origination in stored procedures and/or functions. Create the following objects in your test schema:

In the above example, we have created a function, which generates an error, a procedure that calls function and a PL/SQL block that calls the procedure and traps the exception. We are using the dbms_random function to generate a random number between 1 and 100 and truncating it since we are interested in only an integer. For more on random number generation, you can see our blog post here.

In our example above, we are not handling exception in the function or in procedure. Instead, we are handling a very generalized exception in the PL/SQL block. Following is the result of the execution when we execute the PL/SQL block:

ORA-01476: divisor is equal to zero

PL/SQL procedure successfully completed.

From the result, we don’t know where this exception occurred, in the PL/SQL block, in the procedure or in the function. Example shown is a very small example so we can trace it back but for a huge PL/SQL codes that comprises of lots of modular units, it is difficult to trace where these error(s) occurred.

Dbms_utility package has yet another procedure, that can be used to trace back the error to its origin. Procedure name is format_error_backtrace. Using this procedure we can find out where exactly the error occurred first. Let us execute the changed PL/SQL block:

Here we have used, format_error_backtrace stored procedure to back track the error. Here is the output.

ORA-06512: at “CNFGPKMS.TEST_FUNC”, line 6
ORA-06512: at “CNFGPKMS.TEST_PROC”, line 6
ORA-06512: at line 7

From the exception log, it is very clear that the error first occurred at line 6 in the function TEST_FUNC. Since the function TEST_FUNC was called from the procedure TEST_PROC, the next error occurred at line 6 in procedure TEST_PROC. Final line shows, line 7 of PL/SQL block, from which procedure was called. So we got a complete trace of the error, from where it started and where it ended.

Share this:

Like this:

LikeLoading...

Related

This entry was posted on February 10, 2009 at 3:40 am and is filed under Oracle.
You can follow any responses to this entry through the RSS 2.0 feed.
You can leave a response, or trackback from your own site.