[Webinar] Learn how to a build a cloud-first strategyRegister Now

x
  • Status: Solved
  • Priority: Medium
  • Security: Public
  • Views: 9486
  • Last Modified:

View list Of all procedures in Package Body

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
mqateq
Asked:
mqateq
1 Solution
 
FBIAGENTCommented:
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
 
ramkbCommented:

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
 
FBIAGENTCommented:
RAMESH,

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

thomas
0
Veeam Disaster Recovery in Microsoft Azure

Veeam PN for Microsoft Azure is a FREE solution designed to simplify and automate the setup of a DR site in Microsoft Azure using lightweight software-defined networking. It reduces the complexity of VPN deployments and is designed for businesses of ALL sizes.

 
ramkbCommented:

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
 
syakobsonCommented:
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
 
DanRollinsCommented:
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
 
MindphaserCommented:
Force accepted

** Mindphaser - Community Support Moderator **

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

Featured Post

How to Use the Help Bell

Need to boost the visibility of your question for solutions? Use the Experts Exchange Help Bell to confirm priority levels and contact subject-matter experts for question attention.  Check out this how-to article for more information.

Tackle projects and never again get stuck behind a technical roadblock.
Join Now