Tracking the SQL and its line no from PL/SQL

When we use PL/SQL for processing the data, it is easy to track each SQL that is executing and its line no# associated with the PL/SQL units.

Let’s start with a demo.

demo@ORA11G> create or replace package mypkg
  2  as
  3     procedure p1;
  4     procedure p2;
  5  end;
  6  /

Package created.

demo@ORA11G> create or replace package body mypkg
  2  as
  3     procedure p1 as
  4     begin
  5             for x in (select b1.owner,b2.username
  6                     from all_objects b1, all_users b2)
  7             loop
  8                     null;
  9             end loop;
 10     end;
 11
 12     procedure p2 as
 13     begin
 14             for x in (select b1.owner,b2.username
 15                     from all_objects b1, all_users b2)
 16             loop
 17                     null;
 18             end loop;
 19     end;
 20  end;
 21  /

Package body created.

Say I have a package “mypkg” with a couple of methods in it, now let’s execute this method from a connection.

demo@ORA11G> select userenv('sid') from dual;

USERENV('SID')
--------------
             5

demo@ORA11G> exec mypkg.p2;


While this execution is in-progress, we could easily track the SQL that is currently executing along with its line no# with in the package.

rajesh@ORA11G> select sql_fulltext, program_id, program_line#,
  2          (select object_name
  3            from all_objects
  4            where object_id = program_id) object_name ,
  5        plsql_entry_object_id c1,
  6        plsql_entry_subprogram_id c2,
  7        plsql_object_id  c3,
  8        plsql_subprogram_id c4
  9  from v$session b1,
 10       v$sql b2
 11  where b1.sid = 5
 12  and b1.sql_id = b2.sql_id
 13  and b1.sql_child_number = b2.child_number;

SQL_FULLTEXT         PROGRAM_ID PROGRAM_LINE# OBJECT_NAME          C1         C2      C3            C4
-------------------- ---------- ------------- ------------ ---------- ---------- ---------- ----------
SELECT B1.OWNER,B2.U      88507             5 MYPKG             88506          2
SERNAME FROM ALL_OBJ
ECTS B1, ALL_USERS B2


The PLSQL_ENTRY_OBJECT_ID and PLSQL_ENTRY_SUBPROGRAM_ID from V$SESSION would tell us the name of the package and method that is currently executing.

So the above output from V$SESSION tell us that we are executing second method from the package MYPKG.

rajesh@ORA11G> select owner,object_name,object_type,object_id
  2  from all_objects
  3  where object_id in( 88506,88507 );

OWNER      OBJECT_NAM OBJECT_TYPE           OBJECT_ID
---------- ---------- -------------------- ----------
DEMO       MYPKG      PACKAGE BODY              88507
DEMO       MYPKG      PACKAGE                   88506


Similarly the PROGRAM_ID and PROGRAM_LINE# from the V$SQL provide us the information about the program unit and its line no# to which this SQL is currently located.

So the PROGRAM_ID = 88507 represents that it is a PACKAGE BODY (for the package MYPKG) and the PROGRAM_LINE# represents the line no# at which this SQL is present.

rajesh@ORA11G> column text format a40
rajesh@ORA11G> select line, text
  2  from all_source
  3  where name ='MYPKG'
  4  and type ='PACKAGE BODY'
  5  and owner ='DEMO'
  6  and line <=10
  7  order by line ;

      LINE TEXT
---------- ----------------------------------------
         1 package body mypkg
         2 as
         3      procedure p1 as
         4      begin
         5              for x in (select b1.owner,b2.username
         6                      from all_objects b1, all_users b2)
         7              loop
         8                      null;
         9              end loop;
        10      end;

10 rows selected.

rajesh@ORA11G>