OraFAQ Forum: SQL & PL/SQL » Find the names of all stored proc invoked on execution of the main api

Home » SQL & PL/SQL » SQL & PL/SQL » Find the names of all stored proc invoked on execution of the main api

Show: Today's Messages :: Polls :: Message Navigator
E-mail to friend

Find the names of all stored proc invoked on execution of the main api [message #597120]

Tue, 01 October 2013 04:09

njnbat
Messages: 19
Registered: September 2013

Junior Member

I have a main procedure in oracle which invokes many procedures inside it. These internal procedures also calls functions and procedures inside it.This continues to many levels.
For ex:

Proc A

call c

call d

end............

proc c

call e

call f

end ....................

proc e

call j

call h

end j calls some procedure which internally calls another and so on....................

This loop goes on and on . I want to find the names of all procedures invoked at run time when main api is executed. Is it possible to find all of them using toad ? Is there any tool for doing this ?

Report message to a moderator

Re: Find the names of all stored proc invoked on execution of the main api [message #597123 is a reply to message #597120]

Tue, 01 October 2013 04:25

Michel Cadot
Messages: 68641
Registered: March 2007
Location: Nanterre, France, http://...

Senior Member
Account Moderator

have a look at DBMS_UTILITY.FORMAT_CALL_STACK procedure.

[Updated on: Tue, 01 October 2013 04:28]

Report message to a moderator

Re: Find the names of all stored proc invoked on execution of the main api [message #597127 is a reply to message #597123]

Tue, 01 October 2013 04:40

cookiemonster
Messages: 13920
Registered: September 2008
Location: Rainy Manchester

Senior Member

dbms_profiler is a better bet.
If you have proc1 like:

PROCEDURE proc1 IS
BEGIN
  proc2;
  proc3;
END;

If you call format_call_stack in proc3 it will only show proc1 and proc3 not proc2.
dbms_profiler should show everything.

Report message to a moderator

Re: Find the names of all stored proc invoked on execution of the main api [message #597162 is a reply to message #597127]

Tue, 01 October 2013 07:36

ilikesql
Messages: 3
Registered: October 2013
Location: pune

Junior Member

Hi everybody,

One more simple way i can think is create one temporary table and before start of each proc insert one statement in that table and even after proc got executed.

This will confirm which all procs got called.

Report message to a moderator

Re: Find the names of all stored proc invoked on execution of the main api [message #597163 is a reply to message #597162]

Tue, 01 October 2013 07:45

Michel Cadot
Messages: 68641
Registered: March 2007
Location: Nanterre, France, http://...

Senior Member
Account Moderator

This is what dbms_profiler does, and much more as it gives you the time spent in each part of your code.

Report message to a moderator

Re: Find the names of all stored proc invoked on execution of the main api [message #597164 is a reply to message #597163]

Tue, 01 October 2013 07:50

cookiemonster
Messages: 13920
Registered: September 2008
Location: Rainy Manchester

Senior Member

and you don't have to modify the existing code to use it.

Report message to a moderator

Re: Find the names of all stored proc invoked on execution of the main api [message #597165 is a reply to message #597162]

Tue, 01 October 2013 07:51

njnbat
Messages: 19
Registered: September 2013

Junior Member

Tried this method . But unable to see any records in the table plsql_profiler_units but there is data in plsql_profiler_runs. Any help ?

[Updated on: Tue, 01 October 2013 07:52]

Report message to a moderator

Re: Find the names of all stored proc invoked on execution of the main api [message #597166 is a reply to message #597165]

Tue, 01 October 2013 07:52

cookiemonster
Messages: 13920
Registered: September 2008
Location: Rainy Manchester

Senior Member

then presumably you didn't set it up correctly. Post exactly what you did.

Report message to a moderator

Re: Find the names of all stored proc invoked on execution of the main api [message #597170 is a reply to message #597166]

Tue, 01 October 2013 07:55

njnbat
Messages: 19
Registered: September 2013

Junior Member

At the start of the main procedure i wrote DBMS_PROFILER.START_PROFILER('test','test1',v_run);
End of the procedure and in exception block i wrote DBMS_PROFILER.STOP_PROFILER;
But unable to see data in plsql_profiler_units.
However data is going into plsql_profiler_runs.

Report message to a moderator

Re: Find the names of all stored proc invoked on execution of the main api [message #597171 is a reply to message #597165]

Tue, 01 October 2013 07:58

Michel Cadot
Messages: 68641
Registered: March 2007
Location: Nanterre, France, http://...

Senior Member
Account Moderator

You must call:
dbms_profiler.start_profiler ('<a job name>');
before you call the code you want to trace and
dbms_profiler.stop_profiler;
when you want to end the trace.

Report message to a moderator

Re: Find the names of all stored proc invoked on execution of the main api [message #597174 is a reply to message #597171]

Tue, 01 October 2013 08:01

njnbat
Messages: 19
Registered: September 2013

Junior Member

when i write an anonymous block and use it like below its working.
begin
dbms_profiler.start_profiler ('<a job name>');
proc1;
dbms_profiler.stop_profiler;
end;
However when i write the same code in my stored procedure its still not showing the results in plsql_profiler_units.
I am calling this main procedure from java.

Report message to a moderator

Re: Find the names of all stored proc invoked on execution of the main api [message #597176 is a reply to message #597174]

Tue, 01 October 2013 08:10

Michel Cadot
Messages: 68641
Registered: March 2007
Location: Nanterre, France, http://...

Senior Member
Account Moderator

You must query the tables with the same account that you executed the procedure.
So some questions:
- Which account(s) own(s) the procedure(s)?
- Which account owns profiler tables?
- Which account execute the procedure(s)

Report message to a moderator

Re: Find the names of all stored proc invoked on execution of the main api [message #597183 is a reply to message #597176]

Tue, 01 October 2013 08:19

njnbat
Messages: 19
Registered: September 2013

Junior Member

Schema is "Customer" for table and procedure. Data source in application server has user properties set as "customer" too.

Report message to a moderator

Re: Find the names of all stored proc invoked on execution of the main api [message #597184 is a reply to message #597183]

Tue, 01 October 2013 08:19

cookiemonster
Messages: 13920
Registered: September 2008
Location: Rainy Manchester

Senior Member

application server?
what exactly are you using to run this code?

Report message to a moderator

Re: Find the names of all stored proc invoked on execution of the main api [message #597185 is a reply to message #597184]

Tue, 01 October 2013 08:21

njnbat
Messages: 19
Registered: September 2013

Junior Member

i am using my front end module built in struts to call the api . The application is hosted in weblogic application server. The connection properties in application server is set to use the schema as "customer"
Start profiler is written as the first line inside the main api. Main api is calling many internal api that further calls different api's which goes on .
Stop profiler written in the last line of the main api.

[Updated on: Tue, 01 October 2013 08:22]

Report message to a moderator

Re: Find the names of all stored proc invoked on execution of the main api [message #597576 is a reply to message #597185]

Mon, 07 October 2013 00:22

njnbat
Messages: 19
Registered: September 2013

Junior Member

dbms_profiler gives me all the dependent packages and thanks @cookiemonster for suggesting this method. But is there any way to understand the exact procedure referenced inside these packages. If possible , this will really help me as i can identify all the impact procedures upfront before making changes to the main api.

Report message to a moderator

Re: Find the names of all stored proc invoked on execution of the main api [message #597617 is a reply to message #597576]

Mon, 07 October 2013 03:55

cookiemonster
Messages: 13920
Registered: September 2008
Location: Rainy Manchester

Senior Member

It should give you the line numbers, from that you can work out which procedure/function is being used in a package.

Report message to a moderator

Previous Topic:	Herarchical query
Next Topic:	Case statement

Goto Forum:

-=] Back to Top [=-

[ Syndicate this forum (XML) ] [

]

Current Time: Fri Apr 19 15:36:58 CDT 2024