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!
| Reference: | PL/SQL backtraces for debugging from our JCG partner Lukas Eder at the JAVA, SQL, AND JOOQ blog. |
