Home » SQL & PL/SQL » SQL & PL/SQL » How to find Name of the private procedures/functions inside the package? (Oracle 9i)
How to find Name of the private procedures/functions inside the package? [message #394242] Thu, 26 March 2009 05:15 Go to next message
gurupatham
Messages: 66
Registered: March 2008
Location: Chennai
Member
My requirement is , if i give the package name as input it should display all the procedures/functions that are used in that package. Since procedure/Function that are defined inside the package is not an object , So i could not find it in User_Objects Data Dictionary.

Still i can find out the name of the public procedures/Functions by using the User_Procedures Data dictionary , but Private function/procedures are not avilable.

So Any other way to find out the private procedures/functions that are used in a package?

Re: How to find Name of the private procedures/functions inside the package? [message #394251 is a reply to message #394242] Thu, 26 March 2009 05:27 Go to previous messageGo to next message
Frank
Messages: 7880
Registered: March 2000
Senior Member
Nope, not from the metadata. Only if you create some parser of your own (very difficult job).
Re: How to find Name of the private procedures/functions inside the package? [message #394256 is a reply to message #394242] Thu, 26 March 2009 05:37 Go to previous messageGo to next message
JRowbottom
Messages: 5933
Registered: June 2006
Location: Sunny North Yorkshire, ho...
Senior Member
One of the points of having a package is to only make certain functions/procedures publically available. This allows you to simplify the public interface to the package while still retaining the flexibility to have a complex inside it.

If you take advantage of the advance declaration functionality, and pre-declare all your procedure/functions at the start of the package body, then you get a neat record of the code items.

Why do you need to do this - if you explain why you need this, we might be able to think of another solution.
Re: How to find Name of the private procedures/functions inside the package? [message #394262 is a reply to message #394256] Thu, 26 March 2009 05:50 Go to previous messageGo to next message
gurupatham
Messages: 66
Registered: March 2008
Location: Chennai
Member
I have to write a code to find all the procedures/functions that are used in a Package and display their execution hierarchy.
it is not possible to recode the existing application.
Re: How to find Name of the private procedures/functions inside the package? [message #394269 is a reply to message #394262] Thu, 26 March 2009 06:21 Go to previous messageGo to next message
Mahesh Rajendran
Messages: 10672
Registered: March 2002
Location: oracleDocoVille
Senior Member
Account Moderator
>>I have to write a code to find all the procedures/functions that are used in a Package and display their execution hierarchy.
May be, I am missing something here.
A simple "desc" should do this job.
> desc dbms_output
PROCEDURE DISABLE
PROCEDURE ENABLE
 Argument Name			Type			In/Out Default?
 ------------------------------ ----------------------- ------ --------
 BUFFER_SIZE			NUMBER(38)		IN     DEFAULT
PROCEDURE GET_LINE
 Argument Name			Type			In/Out Default?
 ------------------------------ ----------------------- ------ --------
 LINE				VARCHAR2		OUT
 STATUS 			NUMBER(38)		OUT
PROCEDURE GET_LINES
 Argument Name			Type			In/Out Default?
 ------------------------------ ----------------------- ------ --------
 LINES				TABLE OF VARCHAR2(32767) OUT
 NUMLINES			NUMBER(38)		IN/OUT
PROCEDURE GET_LINES
 Argument Name			Type			In/Out Default?
 ------------------------------ ----------------------- ------ --------
 LINES				DBMSOUTPUT_LINESARRAY	OUT
 NUMLINES			NUMBER(38)		IN/OUT
PROCEDURE NEW_LINE
PROCEDURE PUT
 Argument Name			Type			In/Out Default?
 ------------------------------ ----------------------- ------ --------
 A				VARCHAR2		IN
PROCEDURE PUT_LINE
 Argument Name			Type			In/Out Default?
 ------------------------------ ----------------------- ------ --------
 A				VARCHAR2		IN

Re: How to find Name of the private procedures/functions inside the package? [message #394288 is a reply to message #394269] Thu, 26 March 2009 06:53 Go to previous messageGo to next message
gurupatham
Messages: 66
Registered: March 2008
Location: Chennai
Member
Thank you for your reply. But my requirement is , need to find private procedure/functions too.
Moreover i want to do it in a PLSQL Code.
Is there any other method to find those private procedures/Functions?
Re: How to find Name of the private procedures/functions inside the package? [message #394301 is a reply to message #394288] Thu, 26 March 2009 07:23 Go to previous messageGo to next message
pablolee
Messages: 2834
Registered: May 2007
Location: Scotland
Senior Member
Frank wrote on Thu, 26 March 2009 10:27
Nope, not from the metadata. Only if you create some parser of your own (very difficult job).

Re: How to find Name of the private procedures/functions inside the package? [message #394484 is a reply to message #394301] Fri, 27 March 2009 06:17 Go to previous messageGo to next message
JRowbottom
Messages: 5933
Registered: June 2006
Location: Sunny North Yorkshire, ho...
Senior Member
If none of the procedures and functions in your package contain any procedures/ functions inside them, then you should be able to look through USER/ALL Source for lines starting PROCEDURE/FUNCTION.

you may well have to find some way to deal with comments, and advance declarations of functions, and if you overload your procedures/functions then that will give you problems too.

Trying to extrat an execution hierarchy is going to be even trickier. You'll have to look through the code, matching up the start and ends of each proc/func, and then looking for lines between those two points that refer to
i) another private proc/func in this package, or
ii) a publically visible object in this , or anther schema.

Re: How to find Name of the private procedures/functions inside the package? [message #394768 is a reply to message #394484] Mon, 30 March 2009 04:32 Go to previous messageGo to next message
ramoradba
Messages: 2454
Registered: January 2009
Location: AndhraPradesh,Hyderabad,I...
Senior Member
Please go through this....

http://www.orafaq.com/forum/m/390943/136607/#msg_390943

Thanks & regards
Sriram
Re: How to find Name of the private procedures/functions inside the package? [message #404290 is a reply to message #394768] Thu, 21 May 2009 04:53 Go to previous messageGo to next message
sr_orcl
Messages: 82
Registered: January 2009
Location: mumbai
Member


try,
User_Arguments view.
fro finding the pakage private procedures and functions.

[Mod Edit: JI removed pointless code tags]

[Updated on: Thu, 21 May 2009 04:56] by Moderator

Report message to a moderator

Re: How to find Name of the private procedures/functions inside the package? [message #404293 is a reply to message #394242] Thu, 21 May 2009 04:57 Go to previous message
cookiemonster
Messages: 12407
Registered: September 2008
Location: Rainy Manchester
Senior Member
You can try, but you won't succeed.
The *_arguments views don't contain that data.
Previous Topic: selecting random recordset
Next Topic: How to Output the updated row
Goto Forum:
  


Current Time: Tue Dec 06 08:27:08 CST 2016

Total time taken to generate the page: 0.15219 seconds