Re: Oracle Procedures and Packages

From: Thomas Kyte <tkyte_at_us.oracle.com>
Date: 1997/07/25
Message-ID: <33dbf6c8.20545823@newshost>#1/1

On Fri, 25 Jul 1997 16:43:47 GMT, psmith_at_teleglobe.co.uk (Paul Smith) wrote:

>I'm in need of an answer to the following question:
>
>Given an Oracle7 procedure or function name, is it possible to
>determine the following:-
> a) The name of the enclosing package (if any)

A is ambigous. Consider:

create package foo
as

        procedure bar;
end;

create package abc
as

        procedure bar;
end;

create procedure bar....

You can get all of the packages and standalone versions via:

select distinct package_name, 'PACKAGE'
  from user_arguments
 where object_name = 'BAR'
union all
select object_name, object_type,
  from user_objects
 where object_type in ( 'PROCEDURE', 'FUNCTION' )    and object_name = 'BAR'

This will return all package names BAR is in as well as any occurrences of the standalone procedure/function BAR

> b) If it is a freestanding procedure or function

If is it in user_objects (or all_objects) and type is procedure/function....

> c) If it exists on the database at all

See A.

>
>Thanks in advance,
>Paul.

Thomas Kyte
tkyte_at_us.oracle.com
Oracle Government
Bethesda MD

http://govt.us.oracle.com/ -- downloadable utilities

---

Opinions are mine and do not necessarily reflect those of Oracle Corporation Received on Fri Jul 25 1997 - 00:00:00 CDT