Solved

View list Of all procedures in Package Body

Posted on 2001-09-07
7
9,326 Views
Last Modified: 2007-11-27
I want to get a list of all procedures from a package body
is there any table (ALL_... or USER_...) available to query the list???  
0
Comment
Question by:mqateq
[X]
Welcome to Experts Exchange

Add your voice to the tech community where 5M+ people just like you are talking about what matters.

  • Help others & share knowledge
  • Earn cash & points
  • Learn & ask questions
7 Comments
 
LVL 5

Accepted Solution

by:
FBIAGENT earned 25 total points
ID: 6464889
You may use the following query to obtain the package specification (if you're not the package owner)

select text from all_source
where name = 'packagename'
;

hope it helps
0
 
LVL 3

Expert Comment

by:ramkb
ID: 6464914

Hi,

Just describe the package.  This will give you the list of functions, procedures in the package with parameters and datatype.

SQL> DESC packagename


If you want to see the contents of the procedures, you can select from user_source
where type = 'PACKAGE BODY'
and name = packagename
/

Cheers,
Ramesh
0
 
LVL 5

Expert Comment

by:FBIAGENT
ID: 6464958
RAMESH,

DESCRIBE packagename will not work.  In SQL*Plus, you can only DESCRIBE <packagename>.<procedure>

thomas
0
Technology Partners: We Want Your Opinion!

We value your feedback.

Take our survey and automatically be enter to win anyone of the following:
Yeti Cooler, Amazon eGift Card, and Movie eGift Card!

 
LVL 3

Expert Comment

by:ramkb
ID: 6465375

Hi Thomas,

Well, this depends on the version of database you are using.  If you are still using 7.x (or older), the only way is to look at user_source.

But with Oracle 8 or above, you can just do a
DESC <packagename>

Hope this helps..
- Ramesh
0
 
LVL 4

Expert Comment

by:syakobson
ID: 6467158
If you own the package, use:

SELECT  DISTINCT OBJECT_NAME  
  FROM  USER_ARGUMENTS
  WHERE PACKAGE_NAME='YOUR-PACKAGE_NAME';

This will give you a list of all procedures and functions in a package. Note that overloaded procedure/function will appear only once:

SQL> CREATE OR REPLACE
  2  PACKAGE PKG1
  3  IS
  4  PROCEDURE X;
  5  PROCEDURE X(N NUMBER);
  6  FUNCTION X RETURN BOOLEAN;
  7  FUNCTION X(N NUMBER) RETURN NUMBER;
  8  END;
  9  /

Package created.

SQL> SELECT  DISTINCT OBJECT_NAME
  2    FROM  USER_ARGUMENTS
  3    WHERE PACKAGE_NAME = 'PKG1';

OBJECT_NAME
------------------------------
X

If you want to know if it is a function or procedure, use:

SELECT  DISTINCT OBJECT_NAME,
        DECODE(POSITION,0,'FUNCTION','PROCEDURE')
  FROM  USER_ARGUMENTS
  WHERE PACKAGE_NAME='YOUR-PACKAGE_NAME';

Note, that procedure overloaded with function will appear twice: as procedure and as function:

SQL> SELECT  DISTINCT OBJECT_NAME,
  2          DECODE(POSITION,0,'FUNCTION','PROCEDURE')
  3    FROM  USER_ARGUMENTS
  4    WHERE PACKAGE_NAME = 'PKG1';

OBJECT_NAME                    DECODE(PO
------------------------------ ---------
X                              FUNCTION
X                              PROCEDURE

If you are not packagae owner but have EXECUTE privilege on a package, use:

SELECT  DISTINCT OBJECT_NAME  
  FROM  ALL_ARGUMENTS
  WHERE PACKAGE_NAME='YOUR-PACKAGE_NAME'
    AND OWNER = 'PACKAGE-OWNER';

or

SELECT  DISTINCT OBJECT_NAME,
        DECODE(POSITION,0,'FUNCTION','PROCEDURE')
  FROM  USER_ARGUMENTS
  WHERE PACKAGE_NAME='YOUR-PACKAGE_NAME'
   AND OWNER = 'PACKAGE-OWNER';

Solomon Yakobson.


       
0
 
LVL 49

Expert Comment

by:DanRollins
ID: 7064015
Hi mqateq,
It appears that you have forgotten this question. I will ask Community Support to close it unless you finalize it within 7 days. If there is no objection or further activity, I will suggest to:

    Split points between: FBIAGENT and ramkb

mqateq, if you think your question was not answered at all or if you need help, you can simply post a new comment here.  Community Support moderators will followup.

Please do not accept this comment as an answer!

EXPERTS: Your input for closing recommendations are REQUESTED.
==========
DanRollins -- EE database cleanup volunteer
0
 
LVL 6

Expert Comment

by:Mindphaser
ID: 7091410
Force accepted

** Mindphaser - Community Support Moderator **

ramkb, there will be a separate question with points for your help.
0

Featured Post

Free Tool: Site Down Detector

Helpful to verify reports of your own downtime, or to double check a downed website you are trying to access.

One of a set of tools we are providing to everyone as a way of saying thank you for being a part of the community.

Question has a verified solution.

If you are experiencing a similar issue, please ask a related question

Working with Network Access Control Lists in Oracle 11g (part 1) Part 2: http://www.e-e.com/A_9074.html So, you upgraded to a shiny new 11g database and all of a sudden every program that used UTL_MAIL, UTL_SMTP, UTL_TCP, UTL_HTTP or any oth…
Background In several of the companies I have worked for, I noticed that corporate reporting is off loaded from the production database and done mainly on a clone database which needs to be kept up to date daily by various means, be it a logical…
Via a live example show how to connect to RMAN, make basic configuration settings changes and then take a backup of a demo database
This video shows how to configure and send email from and Oracle database using both UTL_SMTP and UTL_MAIL, as well as comparing UTL_SMTP to a manual SMTP conversation with a mail server.

707 members asked questions and received personalized solutions in the past 7 days.

Join the community of 500,000 technology professionals and ask your questions.

Join & Ask a Question