?
Solved

View list Of all procedures in Package Body

Posted on 2001-09-07
7
Medium Priority
?
9,382 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 100 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
What does it mean to be "Always On"?

Is your cloud always on? With an Always On cloud you won't have to worry about downtime for maintenance or software application code updates, ensuring that your bottom line isn't affected.

 
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: Subnet Calculator

The subnet calculator helps you design networks by taking an IP address and network mask and returning information such as network, broadcast address, and host range.

One of a set of tools we're offering 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

This article started out as an Experts-Exchange question, which then grew into a quick tip to go along with an IOUG presentation for the Collaborate confernce and then later grew again into a full blown article with expanded functionality and legacy…
How to Unravel a Tricky Query Introduction If you browse through the Oracle zones or any of the other database-related zones you'll come across some complicated solutions and sometimes you'll just have to wonder how anyone came up with them.  …
This video shows, step by step, how to configure Oracle Heterogeneous Services via the Generic Gateway Agent in order to make a connection from an Oracle session and access a remote SQL Server database table.
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.
Suggested Courses

741 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