Re: can I see the procedures in a package thru SQLPLUS???

"desc packagename" shows all the procedures and describe their arguments, this is not actually the question. More, it does not work in Oracle7.

all_source and dba_source show all the source of the packages. When there are big this is not very usefull to find where is a procedure.

Here's two queries, first lists the procedures of a package, second lists the packages containing a procedure:

select distinct object_name procedure
from all_arguments
where owner = '<your user>' and package_name='<your package>';

select distinct owner, package_name
from all_arguments
where object_name = '<your procedure>';

You can also use directly the sys tables, this is a little faster:

select distinct a.procedure$ as procedure from sys.argument$ a, sys.obj$ o, sys.user$ u

where o.obj# = a.obj#
  and o.owner# = u.user#
  and u.name = '<your user>'
  and o.name = '<your package>';

select distinct u.name as owner, o.name as package_name from sys.argument$ a, sys.obj$ o, sys.user$ u

where o.obj# = a.obj#
  and o.owner# = u.user#
  and a.procedure$ = '<your procedure>';

Note that the search for the packages containing a procedure takes a rather long time in Oracle8 (8.0.5) (10 times Oracle7 7.3.4 in my tests).

--
Have a nice day
Michel


NeilC <neilc-olops_at_btinternet.com> a écrit dans le message :
3816DB7B.3E399DB8_at_btinternet.com...


> You can try doing the following


>

> sql> desc packagename

>

> HTH neil

>

> James Belton wrote:

>

> > I want to be able to see which procedures are stored in a package or even

> > which package a procedure might be in, using SQLPLUS.

> >

> > I've trawled thru the SYS tables and views and can't find anything

> > obvious...... does anybody know how to do this???

>