Hi,
I need to audit/log/catch/whatever_you_call_it actual values for parameters of a particular procedure(s).
Of course it's very simple if there is a possibility to amend procedure a bit but I cannot do that.
What I've tried:
1. event 10046. It captures only values of bind variables. That's not what I need.
2. event 10938/dbms_trace.set_plsql_trace
Unfortunately proc_params is null. Look like a bug though.
create or replace procedure p(v in varchar2, n in number) as
begin
dbms_application_info.set_client_info(v || ' ' || to_char(n));
end;
alter session set events '10928 trace name context forever, level 511';
exec p('qwerty', 111);
SQL> column event_unit format a25
SQL> column event_unit_kind format a20
SQL> column event_comment format a30
SQL> column proc_params format a10
SQL> select event_seq,
2 event_unit,
3 event_unit_kind,
4 event_line,
5 stack_depth,
6 proc_line,
7 proc_params,
8 event_comment
9 from sys.plsql_trace_events
10 where runid = (select max(runid) from sys.plsql_trace_events);
EVENT_SEQ EVENT_UNIT EVENT_UNIT_KIND EVENT_LINE STACK_DEPTH PROC_LINE PROC_PARAM EVENT_COMMENT
---------- ------------------------- -------------------- ---------- ----------- ---------- ---------- ------------------------------
1 PL/SQL Trace Tool started
2 Trace flags changed
3 <anonymous> ANONYMOUS BLOCK 0 0 PL/SQL Virtual Machine started
4 <anonymous> ANONYMOUS BLOCK 1 1 New line executed
5 Unknown Trace Event
6 Unknown Trace Event
7 <anonymous> ANONYMOUS BLOCK 1 1 1 Procedure Call
8 P PROCEDURE 1 2 New line executed
9 P PROCEDURE 2 2 New line executed
10 P PROCEDURE 3 2 New line executed
11 P PROCEDURE 3 2 51 Procedure Call
12 DBMS_APPLICATION_INFO PACKAGE BODY 52 3 New line executed
13 DBMS_APPLICATION_INFO PACKAGE BODY 52 3 PL/SQL Internal Call
14 DBMS_APPLICATION_INFO PACKAGE BODY 52 3 New line executed
15 DBMS_APPLICATION_INFO PACKAGE BODY 52 2 4 Return from procedure call
16 P PROCEDURE 4 2 New line executed
17 P PROCEDURE 4 1 1 Return from procedure call
18 <anonymous> ANONYMOUS BLOCK 1 1 New line executed
19 PL/SQL Virtual Machine stopped
19 rows selected.
3. event 10928. Produces quite detailed trace of assembler like commands but I didn't managed to find param values there.
4. dbms_profiler and more detailed dbms_hprof do not capture passed values. These tools are more focused on tracking of elapsed time.
5. dbms_debug.
Even though it's possible to capture input values from procedure in another session (I can add logon trigger to execute dbms_debug.initialize + dbms_debug.debug_on in target session) it's extremely complicated to implement automated solution.
It requires a breakpoint in each monitored procedure and once any breakpoint reached then dbms_debug.continue should be issued.
Also I assume that it will degrade performance.
The same pitfalls are inherent to dbms_debug_jdwp.
6. AUDIT (audit execute on p).
Unfortunately I didn't managed to find params values in audit tables like dba_audit_trail etc.
Well, as a conclusion I didn't find any feasible approach to capture passed values to stored procedure (it's possible to create additional objects like logon triggers but it's impossible to change the source).
Maybe there is a way to populate plsql_trace_events.proc_params?
Or maybe exists some event to log them into trace file?
Or maybe it's possible to enable more detailed audit and view details of it?
Any help would be appreciated.
Thanks