Systems Engineering and RDBMS

Tracing the error in a Stored Procedure/Function

Posted by decipherinfosys on February 10, 2009

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:

/* Function which returns error*/
CREATE OR REPLACE FUNCTION TEST_FUNC (p_num1 NUMBER)
RETURN NUMBER IS
v_num NUMBER;
BEGIN

v_num := P_num1/0;

RETURN v_num;

END TEST_FUNC;
/

/* Procedure calls function*/
CREATE OR REPLACE PROCEDURE TEST_PROC (p_num1 NUMBER)
IS
v_num1 NUMBER(9);
BEGIN

v_num1 := TEST_FUNC(p_num1);

END TEST_PROC;
/

/* PL/SQL block which calls procedure*/
DECLARE
p_num NUMBER(9);

BEGIN
p_num := TRUNC(dbms_random.value(1,100));
dbms_output.put_line(p_num);
test_proc(p_num);
EXCEPTION
WHEN OTHERS THEN
dbms_output.put_line(SQLERRM);
END;
/

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:

DECLARE
p_num NUMBER(9);

BEGIN
p_num := TRUNC(dbms_random.value(1,100));
dbms_output.put_line(p_num);
test_proc(p_num);
EXCEPTION
WHEN OTHERS THEN
dbms_output.put_line(dbms_utility.format_error_backtrace);
END;
/

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.

Leave a Reply

Fill in your details below or click an icon to log in:

WordPress.com Logo

You are commenting using your WordPress.com account. Log Out / Change )

Twitter picture

You are commenting using your Twitter account. Log Out / Change )

Facebook photo

You are commenting using your Facebook account. Log Out / Change )

Google+ photo

You are commenting using your Google+ account. Log Out / Change )

Connecting to %s

 
%d bloggers like this: