Solved

Tracking all procedures calls with related parameters in Oracle.

Posted on 2008-10-25
6
707 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
[X]
Welcome to Experts Exchange

Add your voice to the tech community where 5M+ people just like you are talking about what matters.

  • Help others & share knowledge
  • Earn cash & points
  • Learn & ask questions
  • 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
Revamp Your Training Process

Drastically shorten your training time with WalkMe's advanced online training solution that Guides your trainees to action.

 
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

Independent Software Vendors: We Want Your Opinion

We value your feedback.

Take our survey and automatically be enter to win anyone of the following:
Yeti Cooler, Amazon eGift Card, and Movie eGift Card!

Question has a verified solution.

If you are experiencing a similar issue, please ask a related question

Suggested Solutions

Title # Comments Views Activity
how to trim oracle sql sentence in unix 17 87
Oracle mutateing errors 3 26
looking for guidance on Oracle Sql Formatting standards 9 45
Oracle cluster . 1 24
Note: this article covers simple compression. Oracle introduced in version 11g release 2 a new feature called Advanced Compression which is not covered here. General principle of Oracle compression Oracle compression is a way of reducing the d…
Have you ever had to make fundamental changes to a table in Oracle, but haven't been able to get any downtime?  I'm talking things like: * Dropping columns * Shrinking allocated space * Removing chained blocks and restoring the PCTFREE * Re-or…
Video by: Steve
Using examples as well as descriptions, step through each of the common simple join types, explaining differences in syntax, differences in expected outputs and showing how the queries run along with the actual outputs based upon a simple set of dem…
This video shows how to configure and send email from and Oracle database using both UTL_SMTP and UTL_MAIL, as well as comparing UTL_SMTP to a manual SMTP conversation with a mail server.

734 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