How to print line numbers in PL/SQL

Introduction

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.

DBMS_UTILITY.FORMAT_ERROR_BACKTRACE

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.

Conclusion

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.