Results 1 to 7 of 7
  1. #1
    Join Date
    Sep 2009
    Posts
    21

    Unanswered: A way to view all procedures/functions in all packages?

    Im new to oracle and am using 9i for some work.

    Is there a way to list all procedures and functions that are declared in all packages in a database?

  2. #2
    Join Date
    Aug 2003
    Location
    Where the Surf Meets the Turf @Del Mar, CA
    Posts
    7,776
    Provided Answers: 1
    depending upon your exact needs, below should point you in the right direction

    Code:
    SQL> desc dba_source
     Name					   Null?    Type
     ----------------------------------------- -------- ----------------------------
     OWNER						    VARCHAR2(30)
     NAME						    VARCHAR2(30)
     TYPE						    VARCHAR2(12)
     LINE						    NUMBER
     TEXT						    VARCHAR2(4000)
    
    SQL> select type, count(*) from dba_source group by type order by 1;
    
    TYPE	       COUNT(*)
    ------------ ----------
    FUNCTION	   1276
    PACKAGE 	 127314
    PACKAGE BODY	 139929
    PROCEDURE	   6220
    TRIGGER 	   6071
    TYPE		  17716
    TYPE BODY	   3349
    
    7 rows selected.
    You can lead some folks to knowledge, but you can not make them think.
    The average person thinks he's above average!
    For most folks, they don't know, what they don't know.
    Good judgement comes from experience. Experience comes from bad judgement.

  3. #3
    Join Date
    Sep 2009
    Posts
    21
    unless I am missing something, looking at that select statement, I can obtain a list of all the packages in the database...but is there a way such that for each package, I can extract all the function/procedure headers and list them? and generate a view that has all the function/procedure headers extracted from every package

  4. #4
    Join Date
    Aug 2003
    Location
    Where the Surf Meets the Turf @Del Mar, CA
    Posts
    7,776
    Provided Answers: 1
    >I can extract all the function/procedure headers and list them?
    I am not sure what exactly you mean by "headers".

    sqlplus provides the "DESCRIBE" command as in below

    Code:
    SQL> desc dbms_lock
    PROCEDURE ALLOCATE_UNIQUE
     Argument Name			Type			In/Out Default?
     ------------------------------ ----------------------- ------ --------
     LOCKNAME			VARCHAR2		IN
     LOCKHANDLE			VARCHAR2		OUT
     EXPIRATION_SECS		NUMBER(38)		IN     DEFAULT
    FUNCTION CONVERT RETURNS NUMBER(38)
     Argument Name			Type			In/Out Default?
     ------------------------------ ----------------------- ------ --------
     ID				NUMBER(38)		IN
     LOCKMODE			NUMBER(38)		IN
     TIMEOUT			NUMBER			IN     DEFAULT
    FUNCTION CONVERT RETURNS NUMBER(38)
     Argument Name			Type			In/Out Default?
     ------------------------------ ----------------------- ------ --------
     LOCKHANDLE			VARCHAR2		IN
     LOCKMODE			NUMBER(38)		IN
     TIMEOUT			NUMBER			IN     DEFAULT
    FUNCTION RELEASE RETURNS NUMBER(38)
     Argument Name			Type			In/Out Default?
     ------------------------------ ----------------------- ------ --------
     ID				NUMBER(38)		IN
    FUNCTION RELEASE RETURNS NUMBER(38)
     Argument Name			Type			In/Out Default?
     ------------------------------ ----------------------- ------ --------
     LOCKHANDLE			VARCHAR2		IN
    FUNCTION REQUEST RETURNS NUMBER(38)
     Argument Name			Type			In/Out Default?
     ------------------------------ ----------------------- ------ --------
     ID				NUMBER(38)		IN
     LOCKMODE			NUMBER(38)		IN     DEFAULT
     TIMEOUT			NUMBER(38)		IN     DEFAULT
     RELEASE_ON_COMMIT		BOOLEAN 		IN     DEFAULT
    FUNCTION REQUEST RETURNS NUMBER(38)
     Argument Name			Type			In/Out Default?
     ------------------------------ ----------------------- ------ --------
     LOCKHANDLE			VARCHAR2		IN
     LOCKMODE			NUMBER(38)		IN     DEFAULT
     TIMEOUT			NUMBER(38)		IN     DEFAULT
     RELEASE_ON_COMMIT		BOOLEAN 		IN     DEFAULT
    PROCEDURE SLEEP
     Argument Name			Type			In/Out Default?
     ------------------------------ ----------------------- ------ --------
     SECONDS			NUMBER			IN
    Is this what you desire?
    If not provide example of what you really want.
    You can lead some folks to knowledge, but you can not make them think.
    The average person thinks he's above average!
    For most folks, they don't know, what they don't know.
    Good judgement comes from experience. Experience comes from bad judgement.

  5. #5
    Join Date
    Sep 2009
    Posts
    21
    hmmm that kind of looks like what I want...specifically I was aiming at say I have Two schema

    One
    Two

    Where each schema each had a package, Say A for schema One, and B for schemaTwo

    Package A encapsulates
    procedure Apple
    procedure Bananna

    Package B encapsulates
    function Cherry
    procedure Grape

    I wanted get a query that had columns

    schema Name, Package Name, Type, Type Name

    For the example above
    One, A, procedure, Apple
    One, A, procedure, Bannana
    Two, B, function, Cherry
    Two, B, procedure, grape

    where I wanted to hit all the packages that exist in a database

    EDIT:meant schema, not table

  6. #6
    Join Date
    Aug 2003
    Location
    Where the Surf Meets the Turf @Del Mar, CA
    Posts
    7,776
    Provided Answers: 1
    I am unaware of any easy way to produce what you desire.
    The solution depends upon how much effort you are willing to expend.
    Perhaps others here can easily produce what you desire.
    I just do not know of any vanilla SQL to produce a list of procedures and/or functions within any package.

    Good Luck!
    You can lead some folks to knowledge, but you can not make them think.
    The average person thinks he's above average!
    For most folks, they don't know, what they don't know.
    Good judgement comes from experience. Experience comes from bad judgement.

  7. #7
    Join Date
    Nov 2003
    Posts
    2,935
    Provided Answers: 12
    Quote Originally Posted by fff398
    Is there a way to list all procedures and functions that are declared in all packages in a database?
    Something along the lines:
    Code:
    SELECT DISTINCT p.owner,
           p.object_name AS package_name,
           p.procedure_name
    FROM all_procedures p 
         JOIN all_objects o ON (o.owner = p.owner AND o.object_name = p.object_name AND o.object_type = 'PACKAGE')
    ORDER BY 1,2,3;

Posting Permissions

  • You may not post new threads
  • You may not post replies
  • You may not post attachments
  • You may not edit your posts
  •