Re: Associating two queries

The dynamic SQL run by "execute immediate" in a stored procedure has program_id of v$sql pointing to the procedure, and program_line# identifies the line in dba_source. Using your example (except created in my schema instead of sys)

SQL> CREATE OR REPLACE PROCEDURE EXECSOMETHING(P VARCHAR) AS   2 BEGIN
  3 EXECUTE IMMEDIATE P;
  4 END;
  5 /

Procedure created.

SQL> EXEC EXECSOMETHING('BEGIN DBMS_OUTPUT.PUT_LINE(''FOOBAR''); END;'); PL/SQL procedure successfully completed.

Then I see these SQLs in v$sql (I flushed shared pool first to make the output cleaner):

SQL> select sql_id, sql_text from v$sqlarea where lower(sql_text) like '%dbms_output%' and sql_text not like 'select % v$%';

SQL_ID SQL_TEXT
------------- --------------------------------------------------------------------------------
b9c6ffh8tc71f BEGIN dbms_output.enable(NULL); END; <-- ignore; from somewhere else 3083hkyavt9g8 BEGIN EXECSOMETHING('BEGIN DBMS_OUTPUT.PUT_LINE(''FOOBAR''); END;'); END; <-- PL/SQL "parent" cxms51ba537p0 BEGIN DBMS_OUTPUT.PUT_LINE('FOOBAR'); END; <-- native dynamic SQL "child"

SQL> select sql_id, PROGRAM_ID,PROGRAM_LINE# from v$sql where sql_id in ('3083hkyavt9g8','cxms51ba537p0');

SQL_ID PROGRAM_ID PROGRAM_LINE#
------------- ---------- -------------

3083hkyavt9g8          0             0  <-- "parent"
cxms51ba537p0     106865             3  <-- "child"

SQL> select owner, object_name, object_type from dba_objects where object_id = 106865;

OWNER      OBJECT_NAME                    OBJECT_TYPE


---------- ------------------------------ -----------------------

YONG       EXECSOMETHING                  PROCEDURE

SQL> select line, text from dba_source where owner = 'YONG' and name = 'EXECSOMETHING';

      LINE TEXT
---------- --------------------------------------------------------------------------------

         1 PROCEDURE EXECSOMETHING(P VARCHAR) AS
         2 BEGIN
         3 EXECUTE IMMEDIATE P;  <-- line# 3 where the "child" is run
         4 END;

An arbitrary dynamic SQL run by the procedure execsomething can be identified by the known program_id and program_line#. An arbitrary procedure or any PL/SQL stored program that can launch a native dynamic SQL can be found by searching dba_source for "execute immediate". But if it's anonymous block, you have to search v$sql for the block and dba_source for the line. Obviously the SQLs have to still exist in shared pool. Beginning with 11g, cursor invalidation won't age them out and program_id and program_line# columns retain their values on invalidation.

--

http://www.freelists.org/webpage/oracle-l Received on Tue Jun 16 2015 - 17:45:00 CEST