Quick method to catch/capture Stored Procedure calls (with parameters) in Oracle?

Question

I need to capture Oracle stored procedures calls (with parameters) to trace an application (which uses JDBC to connect to the DB). I need something like sp_trace_setevent for Rpc:Completed event in MS SQL SERVER.

I do not have access to this application, but have mostly all rights in the database. I would like to stay in PL/SQL (and Oracle SQL Developer 3.2.20).

I have tried:

1. Oracle SQL Developer UI "Tools"/ "RealTime SQL Monitoring" and "Tools"/ "Sessions" instruments but can't understand how to enabling accumulating information instead of capturing moment snapshot.

2. exploring v$sql - it seems there are no sp calls.

3. v$sqlarea differences (Oracle: is there a tool to trace queries, like Profiler for sql server? , mdj3884 reply) - there I am able to find my test call, but without parameters...

4. Suggestion from Tom's article : http://asktom.oracle.com/pls/asktom/f?p=100:11:0::::P11_QUESTION_ID:767025833873. Particularly, it is looping through v$sqltext_with_newlines, but I can't understand what is a script result. Something more like moment snapshot; isn't it? But then why they call it tracking?

5. use DBMS_APPLICATION_INFO - as I understand using this I can add custom info to V$SESSION and V$SESSION_LONGOPS - it can be useful for monitoring tasks but I can't imagine how it can be used for accumulating information about sp calls and theirs parameters.

6. use DBMS_MONITOR for enabling tracing into file. but I can't find option to enable tracing only sp call events, also it requires access to the server files.

7. DBMS_PROFILER - collects as I understand by default it collects only statistics (min, max time); there should be possibility to add custom information to plsql_profiler_runs but I can't find this table (when DBMS_PROFILER is in place).

What to see next? What I have missed?

P.S. If the only one way is to change SP body (those SP which need to be traced) then what is the quickest and safest way to log sp parameters from sp body in Oracle? It could be logging to custom table, but may be I could choose between generating another types of events (that are not rollbacked, something like SQL Server custom trace events)?

Answer 1

I used DBMS_AUDIT trail to determine what kind of procedures/functions/packages used when a client side application executed. I would really recommend you to use it once, it really helps, but problem is that you cannot analise deeper in a package hierarchy, its function/procedure called (calls), but only its usage. If you want to know dependency of the package you can use ALL_DEPENDENCIES. It can be helpful.

Answer 2

It would be easy to add some custom functionality to do this (see below for most of what is required) or you could use sqltrace or the enterprise manager reports and search through them:

create package p_audit as 
 type t_param_type is table of varchar2(50) index by binary_integer;

 procedure p_audit (p_procedure varchar2, l_param_type t_param_type);
end;

create table audit_table (procedure_name varchar2(50), parameters varchar2(500))

create or replace package body p_audit is

procedure p_audit (p_procedure varchar2, l_param_type t_param_type) is
  pragma autonomous_transaction;  
begin
  insert into audit_table values (p_procedure,l_param_type(1));
  commit;
end;

end p_audit;

declare
  l_param_type p_audit.t_param_type;
begin
  l_param_type(1):='parameter 1';
  p_audit.p_audit('test procedure',l_param_type);
end;