Re: SQL to list a user's packages and its procedures

From: DA Morgan <damorgan_at_psoug.org>
Date: Fri, 08 Dec 2006 08:34:37 -0800
Message-ID: <1165595664.925251@bubbleator.drizzle.com>

Jeremy wrote:

> In article <1165578369.936647.297080_at_73g2000cwn.googlegroups.com>, 
> sybrandb says...

>>
>> On Dec 8, 12:36 pm, Jeremy <jeremy0..._at_gmail.com> wrote:
>>> I know I can do in sqlplus
>>>
>>> SQL> DESC PKG
>>>
>>> I know I can
>>>
>>> SQL> select object_name from user_objects where
>>> object_type='PACKAGE_BODY';
>>>
>>> I know I can
>>>
>>> SQL> select name, text from user_source where name like 'XX%'
>>>
>>> None of these gives me a clean way to return (from a select) the package
>>> name and its procedures.
>>>
>>> What object do I need to query to get back a result such as:
>>>
>>> PACKAGE_NAME TYPE PROC_OR_FUNCTION
>>> ------------ ---- ----------------
>>> PKG1 FUNCTION FNC1
>>> PKG1 FUNCTION FNC2
>>> PKG1 FUNCTION FNC3
>>> PKG1 PROCEDURE PROC1
>>>
>>> ?
>>>

> 

>>
>> select distinct package_name, object_name from user_arguments

> 
> That'll do fine thanks Sybrand. I wonder though.. is there away to 
> determine whether  the object_name returned in this query is a function 
> or a procedure? I know the view doesn't contain that detail.
> 
> regards

No because Sybrand's query answers the question you asked which was about packages. If you want that level of detail then you need to also query the OBJECT_NAME column and specifically, for functions, look for IN_OUT = OUT where POSITION=0.

And don't forget about overloading. You will likely want to separate overloads.

-- 
Daniel A. Morgan
University of Washington
damorgan_at_x.washington.edu
(replace x with u to respond)
Puget Sound Oracle Users Group
www.psoug.org

Received on Fri Dec 08 2006 - 10:34:37 CST