I have the following Stored Procedure:

create or replace procedure insert_employee_to_dept (f_name IN varchar2, l_name IN varchar2, dept IN varchar2, tier IN char)
is
    dept_count number;
    t1_count number;
begin
    INSERT INTO employees (id, first_name, last_name, department, tier)
        VALUES (employee_sequence.NEXTVAL, 
                f_name,
                l_name, 
                dept,
                tier);
    select count(*) into dept_count from employees where department = dept;
    update dept_info set emp_count = dept_count where id = dept;
    select count(*) into t1_count from employees where tier = 1;
    update company set tier_one_count = t1_count where name = 'MyCompany';
end;

I enable SQL ID feedback using below statement

set feedback on sql_id;

Finally I run my Stored Procedure as such:

call insert_employee_to_dept('John', 'Doe', 'Finance', '5')

The output of the above command contains the SQL id of the PL/SQL block but this block does not have an execution block which can be queried using DBMS_XPLAN.DISPLAY_CURSOR

Is there a way to get all the SQL ID's that were executed when the stored procedure was called?

Following this answer, I enabled tracing and ran the procedure which generated the trace file on the server. Unfortunately in my use case I do not have access the file system of the database server

CodePudding user response：

If you doing that for testing purpose, you can use dbms_application_info to set module and action, and then use them to select from v$sql/v$sqlarea/v$active_session_history/etc:

SQL> declare
  2     n number;
  3  begin
  4     dbms_application_info.set_module('test_module','test_action');
  5     select/* test1*/ count(*) into n from dual;
  6     select/* test2*/ count(*) into n from dual;
  7     dbms_application_info.set_module('','');
  8  end;
  9  /

PL/SQL procedure successfully completed.

SQL> select sql_id,substr(sql_text,1,50) sqltext50
  2  from v$sqlarea
  3  where module='test_module'
  4    and action='test_action';

SQL_ID        SQLTEXT50
------------- ------------------------------------
bavxnddfrxju6 SELECT/* test2*/ COUNT(*) FROM DUAL
7d8853usybzdg SELECT/* test1*/ COUNT(*) FROM DUAL

Btw, you can use module/action/client_id also for dbms_monitor: Performing Application Tracing DBMS_MONITOR

Or sometimes you can even use v$open_cursor (depends on many factors like open_cursor parameter, etc):

SQL> declare
  2     n number;
  3  begin
  4     dbms_application_info.set_module('test_module','test_action');
  5     select/* test10*/ count(*) into n from dual;
  6     select/* test11*/ count(*) into n from dual;
  7     dbms_application_info.set_module('','');
  8  end;
  9  /

PL/SQL procedure successfully completed.

SQL> select sql_id,substr(sql_text,1,50) sqltext50
  2  from v$open_cursor
  3  where sid=userenv('sid')
  4  and user_name=user
  5  and last_sql_active_time is not null
  6  order by last_sql_active_time desc
  7  fetch first 5 rows only;

SQL_ID        SQLTEXT50
------------- ---------------------------------------------------
3669hp0tndbgu declare    n number; begin    dbms_application_inf
9szagfb62bhgs SELECT/* test10*/ COUNT(*) FROM DUAL
9h8pabdtrb1wm select sql_id,substr(sql_text,1,50) sqltext50 from
1901dfp1ktg5d SELECT/* test11*/ COUNT(*) FROM DUAL
bavxnddfrxju6 SELECT/* test2*/ COUNT(*) FROM DUAL

CodePudding user response：

You may use all_statements system view to get all the statements executed in PL/SQL unit and their SQL_ID's.

create table t (
  id int,
  val int
)
/

create or replace procedure test_proc(
  p_id in int
)
as
begin
  insert into t(id, val) values(p_id, 100);
  update t set val = 10;
  delete from t where id = p_id;
end;
/

begin
  test_proc(1);
end;
/

select
  owner
  , object_type
  , object_name
  , type
  , line
  , sql_id
  , text
from all_statements
/