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

In article <1165595664.925251_at_bubbleator.drizzle.com>, DA Morgan says...
> 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.

Sybrand's answer was extremely helpful and is actually adequate for what I really need - however the original post quite clearly gave an example of the required information:

i.e.

> >>> PACKAGE_NAME TYPE PROC_OR_FUNCTION
> >>> ------------ ---- ----------------
> >>> PKG1 FUNCTION FNC1
> >>> PKG1 FUNCTION FNC2
> >>> PKG1 FUNCTION FNC3
> >>> PKG1 PROCEDURE PROC1

> 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.
>

Thanks very much for the additional inforamtion.

cheers

-- 
jeremy