Any one who wants to be a good programmer must also acquire good debugging skills. Oracle PL/SQL database programming offers several easy code debugging options through the DBMS_UTILITY package. This article provides some naunces about printing line numbers in PL/SQL code.
One can print the exact line number that caused the PL/SQL error by using the supplied DBMS_FORMAT.FORMAT_ERROR_BACKTRACE procedure. It can be called directly in the dbms_output.put_line procedure. An example is shown below.
How to print line numbers in SQL*Developer
Many Oracle developers use Oracle SQL*Developer IDE (Integrated Development Environment) for working on SQL and PL/SQL code. One can let SQL*Developer display the line numbers by using the TOOLS => Preferences => Code Editor => Line Gutter option
PL/SQL anonymous blocks are always counted from the DECLARE section
One common mistake that PL/SQL programmers do is to assume that the line number shown by the DBMS_FORMAT.FORMAT_ERROR_BACKTRACE is counted from the first line of the code. In the above example, the 1st line number is for the line that contains "set serveroutput on". The PL/SQL code starts only from DECLARE section (line 2). So for the PL/SQL compiler, line 1 is what shown as line 2 in the above screenshot.
The error occurred in PL/SQL code line 7 v_name := 'DBA UNIVERSITY'; (which is line 8 for SQL*Developer). So when debugging anonymous PL/SQL code, one should always count the 1st line from the line that contains DECLARE.
Hope this helps ! We are always looking for your participation in this blog. Please feel free to provide more tips by commenting below to help other readers.