David,
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.
--
//www.freelists.org/webpage/oracle-l
