Home » SQL & PL/SQL » SQL & PL/SQL » Need help to generate Procedure Execution LOG
Need help to generate Procedure Execution LOG [message #594803] Tue, 03 September 2013 05:06 Go to next message
xbubai
Messages: 8
Registered: September 2013
Junior Member
Hi,

I have a very big oracle procedure. Since it's too big and calling many other procedures, I am not able to debug the exceptions thrown. Does anybody know about any oracle utility which logs all the procedures called by the master procedure step by step and maintains a detailed record.
Re: Need help to generate Procedure Execution LOG [message #594805 is a reply to message #594803] Tue, 03 September 2013 05:11 Go to previous messageGo to next message
pablolee
Messages: 2590
Registered: May 2007
Location: Scotland
Senior Member
How will 'maintaining a detailed record' change the fact that you'll still have to debug the code?
Re: Need help to generate Procedure Execution LOG [message #594806 is a reply to message #594805] Tue, 03 September 2013 05:16 Go to previous messageGo to next message
xbubai
Messages: 8
Registered: September 2013
Junior Member
I accept that I still have to debug. But the catch is I am not able to identify even which procedures are being called as all procs are not called inside main procedure..its kinda chain. So if we come to know the procedure names then at least we can directly hit those to debug the issues.
Re: Need help to generate Procedure Execution LOG [message #594807 is a reply to message #594806] Tue, 03 September 2013 05:21 Go to previous messageGo to next message
Roachcoach
Messages: 1125
Registered: May 2010
Location: UK
Senior Member
I believe (it's been a while) that sql developer can step through the code bit by bit so you can see all the calls.
Re: Need help to generate Procedure Execution LOG [message #594810 is a reply to message #594806] Tue, 03 September 2013 05:22 Go to previous messageGo to next message
Littlefoot
Messages: 18821
Registered: June 2005
Location: Croatia, Europe
Senior Member
Account Moderator
Perhaps you could try with something like "who am I?"; have a look at this discussion (as you didn't specify your database version, Michel's suggestion might or might not work, so you'll have to read what Tom Kyte had said).

P.S. Certainly, you'd need to include "whoami" call into every procedure you use and - I'd say - develop it even further, so that it would perform insert into your log table, storing procedure name and a sequence number (so that you could follow the execution). Why sequence and not timestamp? What if two (or more) procedures are called virtually at the same time (i.e. there's no way to know which one was called first)?

[Updated on: Tue, 03 September 2013 05:24]

Report message to a moderator

Re: Need help to generate Procedure Execution LOG [message #594812 is a reply to message #594803] Tue, 03 September 2013 05:45 Go to previous messageGo to next message
Michel Cadot
Messages: 57599
Registered: March 2007
Location: Nanterre, France, http://...
Senior Member
Account Moderator
Quote:
I am not able to debug the exceptions thrown.


If you don't gobble up the exception you should have the whole stack when the exception raises.

Quote:
Does anybody know about any oracle utility which logs all the procedures called by the master procedure step by step and maintains a detailed record.


You may use DBMS_PROFILER.

Regards
Michel
Re: Need help to generate Procedure Execution LOG [message #594818 is a reply to message #594812] Tue, 03 September 2013 07:00 Go to previous messageGo to next message
xbubai
Messages: 8
Registered: September 2013
Junior Member
Hi Michel,
I was going through the oracle docs regarding DBMS_PROFILER. Seems I need to have sysadmin login to access the same. IS it so?
Re: Need help to generate Procedure Execution LOG [message #594819 is a reply to message #594818] Tue, 03 September 2013 07:06 Go to previous messageGo to next message
pablolee
Messages: 2590
Registered: May 2007
Location: Scotland
Senior Member
Where in the documentation does it say that? I don't see it.
Re: Need help to generate Procedure Execution LOG [message #594821 is a reply to message #594819] Tue, 03 September 2013 07:09 Go to previous messageGo to next message
xbubai
Messages: 8
Registered: September 2013
Junior Member
I was checking the below mentioned doc for better understanding and to see how to use the utility.

http://oracleflash.com/44/Write-fast-and-efficient-PLSQL---DBMS_PROFILER.html

for example..
$ cd $ORACLE_HOME/rdbms/admin
$ sqlplus / as sysdba

this is how the user logs in according to the doc.waiting for your confirmation pls
Re: Need help to generate Procedure Execution LOG [message #594823 is a reply to message #594821] Tue, 03 September 2013 07:13 Go to previous messageGo to next message
pablolee
Messages: 2590
Registered: May 2007
Location: Scotland
Senior Member
1. That's not what we would commonly describe as 'The documentation' oft abbreviated to 'Oracle docs', it's a blog. and as such, any statements wrt requirements should be checked against the official documentation.
2. You seem to have scanned over this line in that blog:
conn plsql_prof_test/test

[Updated on: Tue, 03 September 2013 07:15]

Report message to a moderator

Re: Need help to generate Procedure Execution LOG [message #594824 is a reply to message #594821] Tue, 03 September 2013 07:14 Go to previous messageGo to next message
Michel Cadot
Messages: 57599
Registered: March 2007
Location: Nanterre, France, http://...
Senior Member
Account Moderator
1/ This is NOT the official documentation
2/ This is how he logs to INSTALL the package
3/ Read the rest of the page and you will see he reconnects to use it:
Quote:
Now create the user for a test of DBMS_PROFILER.


Regards
Michel

[Updated on: Tue, 03 September 2013 07:14]

Report message to a moderator

Re: Need help to generate Procedure Execution LOG [message #594825 is a reply to message #594824] Tue, 03 September 2013 07:16 Go to previous messageGo to next message
Michel Cadot
Messages: 57599
Registered: March 2007
Location: Nanterre, France, http://...
Senior Member
Account Moderator
Also check if you already have access to the package:
desc dbms_profiler
It should be installed by default (depending on your version you still didn't tell us).

Regards
Michel

[Updated on: Tue, 03 September 2013 07:22]

Report message to a moderator

Re: Need help to generate Procedure Execution LOG [message #594826 is a reply to message #594825] Tue, 03 September 2013 07:18 Go to previous messageGo to next message
xbubai
Messages: 8
Registered: September 2013
Junior Member
Ohh I am so sorry.. I am using 11G.
Re: Need help to generate Procedure Execution LOG [message #594830 is a reply to message #594826] Tue, 03 September 2013 07:23 Go to previous messageGo to next message
pablolee
Messages: 2590
Registered: May 2007
Location: Scotland
Senior Member
And do you already have access to the package or not?
Re: Need help to generate Procedure Execution LOG [message #594831 is a reply to message #594830] Tue, 03 September 2013 07:24 Go to previous messageGo to next message
xbubai
Messages: 8
Registered: September 2013
Junior Member
Nope..just checked... Sad
Re: Need help to generate Procedure Execution LOG [message #594832 is a reply to message #594831] Tue, 03 September 2013 07:27 Go to previous messageGo to next message
pablolee
Messages: 2590
Registered: May 2007
Location: Scotland
Senior Member
Do you have the ability to request access to it?
Re: Need help to generate Procedure Execution LOG [message #594833 is a reply to message #594832] Tue, 03 September 2013 07:28 Go to previous messageGo to next message
xbubai
Messages: 8
Registered: September 2013
Junior Member
No way I can get access to these files..whatever I have to have to do with developer grants..like basic DDl,DML grants..
Re: Need help to generate Procedure Execution LOG [message #594834 is a reply to message #594833] Tue, 03 September 2013 07:37 Go to previous messageGo to next message
pablolee
Messages: 2590
Registered: May 2007
Location: Scotland
Senior Member
Well, you say grants, all you need initially is to be granted the privilege to execute that package. In addition I'd say that this is a fairly important package to have access to if you are a developer.
Re: Need help to generate Procedure Execution LOG [message #594835 is a reply to message #594834] Tue, 03 September 2013 07:38 Go to previous messageGo to next message
xbubai
Messages: 8
Registered: September 2013
Junior Member
yes..let me see if I can get hold of this..though thinking negetive..
Re: Need help to generate Procedure Execution LOG [message #594836 is a reply to message #594835] Tue, 03 September 2013 07:40 Go to previous messageGo to next message
pablolee
Messages: 2590
Registered: May 2007
Location: Scotland
Senior Member
I'm still concerned that you cannot tell which procedure raises an error, it sounds like the exception handling is a problem in itself.
Re: Need help to generate Procedure Execution LOG [message #594837 is a reply to message #594836] Tue, 03 September 2013 07:42 Go to previous messageGo to next message
cookiemonster
Messages: 10569
Registered: September 2008
Location: Rainy Manchester
Senior Member
Indeed, it might be an idea to read WHEN OTHERS
Re: Need help to generate Procedure Execution LOG [message #594840 is a reply to message #594837] Tue, 03 September 2013 07:58 Go to previous message
gazzag
Messages: 267
Registered: November 2010
Location: Bristol, UK
Senior Member
Get your DBA to install the package for you and issue the appropriate grants to you.
Previous Topic: CREATE TABLE: tablename with data from subselect (variable)
Next Topic: set transaction use rollback
Goto Forum:
  


Current Time: Wed Apr 16 04:33:14 CDT 2014

Total time taken to generate the page: 0.16673 seconds