Here’s a Quick Way to Get the Line Number in PL/SQL

Tweet

In Oracle 10g, the PLSQL_LINE predefined inquiry directive is a PLS_INTEGER literal value indicating the line number reference to $$PLSQL_LINE in the current program unit. In other words, $$PLSQL_LINE is the number of the line where $$PLSQL_LINE appears in your PL/SQL code.

Another useful predefined inquiry directive is PLSQL_UNIT which is a VARCHAR2 literal value indicating the current source program unit. For a named compilation unit, $$PLSQL_UNIT contains the unit name. For an anonymous block, $$PLSQL_UNIT is NULL.

Now, on to some examples:

From an anonymous block:

EDDIE@XE> BEGIN
  2     DBMS_OUTPUT.put_line ('Line number: '
  3                           || $$plsql_line);
  4     DBMS_OUTPUT.put_line ('Unit: '
  5                           || COALESCE ($$plsql_unit, 'anonymous block')
  6                          );
  7  END;
  8  /
Line number: 3
Unit: anonymous block

From a procedure:

EDDIE@XE> CREATE OR REPLACE PROCEDURE my_proc
  2  IS
  3  BEGIN
  4     DBMS_OUTPUT.put_line ('Line number: '
  5                           || $$plsql_line);
  6     DBMS_OUTPUT.put_line ('Unit: '
  7                           || $$plsql_unit
  8                          );
  9  END;
 10  /

Procedure created.

EDDIE@XE> exec my_proc;
Line number: 5
Unit: MY_PROC

From a package:

EDDIE@XE> CREATE OR REPLACE PACKAGE my_pkg
  2  IS
  3     PROCEDURE my_proc;
  4  END;
  5  /

Package created.

EDDIE@XE> CREATE OR REPLACE PACKAGE BODY my_pkg
  2  IS
  3     PROCEDURE my_proc
  4     IS
  5     BEGIN
  6        DBMS_OUTPUT.put_line ('Line number: ' || $$plsql_line);
  7        DBMS_OUTPUT.put_line (   'Unit: '
  8                              || $$plsql_unit
  9                             );
 10     END;
 11  END;
 12  /

Package body created.

EDDIE@XE> exec my_pkg.my_proc;
Line number: 6
Unit: MY_PKG

Note that $$PLSQL_UNIT returned the package name, not the procedure name.

A few days ago, I blogged about how to find where an error was raised in PL/SQL using DBMS_UTILITY.FORMAT_ERROR_BACKTRACE which returns a formatted string that displays a stack of programs and line numbers. But, you must parse the returned string to find the line number and program unit name if you want to use them elsewhere in your programs (like storing them in a log table or using them to query the user_source data dictionary table for example). $$PLSQL_LINE and $$PLSQL_UNIT provide this information without string parsing and manipulation. For example:

Using DBMS_UTILITY.FORMAT_ERROR_BACKTRACE:

EDDIE@XE> CREATE OR REPLACE PROCEDURE my_proc
  2  IS
  3  BEGIN
  4     RAISE VALUE_ERROR;
  5  EXCEPTION
  6     WHEN VALUE_ERROR
  7     THEN
  8        DBMS_OUTPUT.put_line (DBMS_UTILITY.format_error_backtrace);
  9  END;
 10  /

Procedure created.

EDDIE@XE> exec my_proc;
ORA-06512: at "EDDIE.MY_PROC", line 4

If you want to extract “MY_PROC” and “4″ from the string ORA-06512: at “EDDIE.MY_PROC”, line 4, you will have to do a little bit of parsing. That is not the case using $$PLSQL_LINE and $$PLSQL_UNIT:

EDDIE@XE> CREATE OR REPLACE PROCEDURE my_proc
  2  IS
  3  BEGIN
  4     DBMS_OUTPUT.put_line ('Line number before the error: ' || $$plsql_line);
  5     RAISE VALUE_ERROR;
  6  EXCEPTION
  7     WHEN VALUE_ERROR
  8     THEN
  9        DBMS_OUTPUT.put_line ( 'Error raised in: '
 10                              || $$plsql_unit
 11                             );
 12  END;
 13  /

Procedure created.

EDDIE@XE> exec my_proc;
Line number before the error: 4
Error raised in: MY_PROC

The inquiry directives PLSQL_LINE and PLSQL_UNIT are useful and easy to use.

Related articles:

• How to find where an error was raised in PL/SQL
• Did you call me?
• Two Quick and Simple Tips That Will Help You Write Better PL/SQL

Filed in Oracle, Tips on 03 Aug 06 | Tags: exception, pl/sql

---