Solved

Tracking all procedures calls with related parameters in Oracle.

Posted on 2008-10-25
6
696 Views
Last Modified: 2013-12-18
I would like to know if there is a way to track all procedure calls in ORACLE PL/SQL environment.

I have 2-3 undocumented packages, so I would like to know how are they used, and with which input parameters.

Do you have any idea?

Please assume you can use SQL Navigator or TOAD, if you find it useful.
0
Comment
Question by:hc2342uhxx3vw36x96hq
  • 3
  • 2
6 Comments
 
LVL 7

Expert Comment

by:DiscoNova
ID: 22807308
I don't know of any "before/after execute of procedure"-triggers, so the "easiest" way is propably to implement the tracking manually into the procedures (supposing of course that you have access to the actual unwrapped procedure bodies). So, for example...
procedure func1 (

  p_var1 integer default null,

  p_var2 varchar2,

  p_var3 date

) is

begin

  -- Whatever...

end;
 

/* ...would be modified into something like this... */
 

procedure func1 (

  p_var1 integer default null,

  p_var2 varchar2,

  p_var3 date

) is

begin

  insert into my_func1_log (log_timestamp, called_by_user, var1, var2, var3) values (sysdate, user, p_var1, p_var2, p_var3);

  commit;

  -- Whatever...

end;
 

/* ...of course, you would need to create the logging table too... */

Open in new window

0
 
LVL 7

Expert Comment

by:DiscoNova
ID: 22807329
However, would it not be easier (assuming again that you have access to the unwrapped procedure bodies) to just read the procedures and document them based on their content? That way you would see what it is actually expecting to get as input well as what it is doing with the provided data... At least I find it easier to understand program based on the code, not based on the "expected input sets" (though sometimes they do in fact offer unprecedented help to understanding what is happening and why). But, your mileage may vary :)
0
 
LVL 1

Author Comment

by:hc2342uhxx3vw36x96hq
ID: 22807441
And if I can't modify the procedures?
0
PRTG Network Monitor: Intuitive Network Monitoring

Network Monitoring is essential to ensure that computer systems and network devices are running. Use PRTG to monitor LANs, servers, websites, applications and devices, bandwidth, virtual environments, remote systems, IoT, and many more. PRTG is easy to set up & use.

 
LVL 7

Accepted Solution

by:
DiscoNova earned 500 total points
ID: 22807655
In that case, I'd say you're pretty much out of luck.

If this were a case of package (since package specification is usually left unwrapped while the package body is wrapped), I'd suggest using some sort of "envelope" that takes similar parameters and simply passes them to the enveloped code (after logging them). But since procedures don't have distinct specification (as it is part of the procedure itself) you don't have access to even that :(

You could try (but I'm not certain how well this works with wrapped procedures) the following query for a bit of reverse engineering:
select

	argument_name,

	in_out,

	data_type,

	defaulted,

	default_value

from

	all_arguments

where

	owner='SCHEMA_OF_PROCEDURE'

	and

	object_name='NAME_OF_PROCEDURE'

order by

	position;

Open in new window

0
 
LVL 3

Expert Comment

by:gajmp
ID: 22823308
alter your session to turn on PL/SQL debug mode for compilation. Issue the below command
ALTER SESSION SET PLSQL_DEBUG=TRUE;
after that compile that SP, fn and pkg with debug option.
after that exeucte the specified sp or fn or pkg and check the trace file
0
 
LVL 1

Author Closing Comment

by:hc2342uhxx3vw36x96hq
ID: 31509913
Thanks!
0

Featured Post

PRTG Network Monitor: Intuitive Network Monitoring

Network Monitoring is essential to ensure that computer systems and network devices are running. Use PRTG to monitor LANs, servers, websites, applications and devices, bandwidth, virtual environments, remote systems, IoT, and many more. PRTG is easy to set up & use.

Join & Write a Comment

Suggested Solutions

Article by: Swadhin
From the Oracle SQL Reference (http://download.oracle.com/docs/cd/B19306_01/server.102/b14200/queries006.htm) we are told that a join is a query that combines rows from two or more tables, views, or materialized views. This article provides a glimps…
I remember the day when someone asked me to create a user for an application developement. The user should be able to create views and materialized views and, so, I used the following syntax: (CODE) This way, I guessed, I would ensure that use…
This video explains at a high level with the mandatory Oracle Memory processes are as well as touching on some of the more common optional ones.
This video shows how to copy an entire tablespace from one database to another database using Transportable Tablespace functionality.

759 members asked questions and received personalized solutions in the past 7 days.

Join the community of 500,000 technology professionals and ask your questions.

Join & Ask a Question

Need Help in Real-Time?

Connect with top rated Experts

19 Experts available now in Live!

Get 1:1 Help Now