Action, Module, Program ID & V$SQL

This Article is about using DBMS_APPLICATION_INFO that pops out PL/SQL module name, action name and line number information in V$Sql.

I'd like to clear up that 10g does in fact also try to inform us of the program (PLSQL module) and line number within that program that first parsed it.  That is, we not only see the first action and module associated with the SQL but also the object_id of the PLSQL module that first parsed it.

scott@10G> create or replace procedure p1
  2  as
  3  begin
  4     dbms_application_info.set_module('module_1','action_1');
  5     for r in (select * from dual look_for_me1)
  6     loop
  7             null;
  8     end loop;
  9
 10     for r in (select * from dual look_for_me2)
 11     loop
 12             null;
 13     end loop;
 14     dbms_application_info.set_module(null,null);
 15  end p1;
 16  /

Procedure created.

scott@10G> create or replace procedure p2
  2  as
  3  begin
  4     dbms_application_info.set_module('module_2','action_2');
  5     for r in (select * from dual look_for_me2)
  6     loop
  7             null;
  8     end loop;
  9  end p2;
 10  /

Procedure created.

scott@10G> exec p2;

PL/SQL procedure successfully completed.

scott@10G> exec p1;

PL/SQL procedure successfully completed.

scott@10G> select object_id,object_name
  2  from user_objects
  3  where object_name in ('P1','P2');

 OBJECT_ID OBJECT_NAME
---------- ------------------------------
     58846 P1
     60806 P2

scott@10G> SELECT sql_text,action,module,program_id,program_line#
  2  FROM V$SQL
  3  where sql_text like '%LOOK%FOR%ME%';

SQL_TEXT                                                   ACTION      MODULE       PROGRAM_ID PROGRAM_LINE#
---------------------------------                          ----------- ------------             ----------         ------------
SELECT * FROM DUAL LOOK_FOR_ME2    action_2   module_2              60806             5
SELECT * FROM DUAL LOOK_FOR_ME1    action_1   module_1              58846             5










The  Program_id & Program_line#  are newly included in V$SQL from Oracle 10g.