Getting the SQL ID's of each statement executed in a stored procedure

Question

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

Maybe [this answer](https://stackoverflow.com/a/66646676/2778710) will be helpful: [Find which PL/SQL Package called a SQL_ID](https://stackoverflow.com/questions/66646391/find-which-pl-sql-package-called-a-sql-id) — astentx, Feb 28 '22 at 23:07
Can you access the trace file through the system views gv$diag_trace_file / gv$diag_trace_file_contents — Gary Myers, Mar 01 '22 at 00:56
@astentx thanks a lot for the reply.. I used the `ALL_STATEMENTS` view and queried by the procedure name and user id and I was able to get the SQL ID's of the statements in by Procedure. Please submit as answer and I will accept — user538578964, Mar 01 '22 at 19:16

score 4 · Accepted Answer · answered Mar 02 '22 at 14:35

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
/

OWNER	OBJECT_TYPE	OBJECT_NAME	TYPE	LINE	SQL_ID	TEXT
DEMO	PROCEDURE	TEST_PROC	DELETE	8	85cd3d95cya5x	DELETE FROM T WHERE ID = :B1
DEMO	PROCEDURE	TEST_PROC	UPDATE	7	d6au94gzv1ydh	UPDATE T SET VAL = 10
DEMO	PROCEDURE	TEST_PROC	INSERT	6	bm74chwppp8w4	INSERT INTO T(ID, VAL) VALUES(:B1 , 100)

Sayan Malakshinov · Answer 2 · 2022-03-01T02:08:54.123

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

Thanks a lot for your answer. Your approach works and I get the required output but it does not fit my use case very well. I have to get the SQL ID of the statements in the procedure given the procedure name only. I cannot change/update any PL/SQL code. This was the only issue with your answer for me. Other than that it works as expected when I followed your instructions. Thanks again! — user538578964, Mar 01 '22 at 19:19

Getting the SQL ID's of each statement executed in a stored procedure

2 Answers2