For many PL/SQL developers, this might be common sense, but for one of our customers, this was an unknown PL/SQL feature: Backtraces.

When your application raises an error somewhere deep down in the call stack, you don’t get immediate information about the exact source of the error. For large PL/SQL applications, this can be a pain. One workaround is to keep track of statement numbers that were last executed before any error occurred:
 
 
 
 

DECLARE
 v_statement_no := 0;
BEGIN
 v_statement_no := 1;
 SELECT ...

 v_statement_no := 2;
 INSERT ...

 v_statement_no := 3;
 ...
EXCEPTION
 WHEN OTHERS THEN
 -- Log error message somewhere
 logger.error(module, v_statement_no, sqlerrm);
END;

The above looks an awful lot like println-debugging, a thing that isn’t really known to Java developers!

But println-debugging isn’t necessary in PL/SQL either. Use the DBMS_UTILITY.FORMAT_ERROR_BACKTRACE function, instead! An example:

DECLARE
 PROCEDURE p4 IS BEGIN
 raise_application_error(-20000, 'Some Error');
 END p4;
 PROCEDURE p3 IS BEGIN
 p4;
 END p3;
 PROCEDURE p2 IS BEGIN
 p3;
 END p2;
 PROCEDURE p1 IS BEGIN
 p2;
 END p1;

BEGIN
 p1;
EXCEPTION
 WHEN OTHERS THEN
 dbms_output.put_line(sqlerrm);
 dbms_output.put_line(
 dbms_utility.format_error_backtrace
 );
END;
/

The above PL/SQL block generates the following output:

ORA-20000: Some Error
ORA-06512: at line 3
ORA-06512: at line 6
ORA-06512: at line 9
ORA-06512: at line 12
ORA-06512: at line 16

You can see exactly what line number generated the error. If you’re not using local procedures in anonymous blocks (which you quite likely aren’t), this gets even more useful:

CREATE PROCEDURE p4 IS BEGIN
 raise_application_error(-20000, 'Some Error');
END p4;
/
CREATE PROCEDURE p3 IS BEGIN
 p4;
END p3;
/
CREATE PROCEDURE p2 IS BEGIN
 p3;
END p2;
/
CREATE PROCEDURE p1 IS BEGIN
 p2;
END p1;
/

BEGIN
 p1;
EXCEPTION
 WHEN OTHERS THEN
 dbms_output.put_line(sqlerrm);
 dbms_output.put_line(
 dbms_utility.format_error_backtrace
 );
END;
/

The above now outputs:

ORA-20000: Some Error
ORA-06512: at "PLAYGROUND.P4", line 2
ORA-06512: at "PLAYGROUND.P3", line 2
ORA-06512: at "PLAYGROUND.P2", line 2
ORA-06512: at "PLAYGROUND.P1", line 2
ORA-06512: at line 2

To learn more about the DBMS_UTILITY package, please consider the manual. True to the nature of all things called “UTILITY”, it really contains pretty much random things that you wouldn’t expect there!