Solved

View list Of all procedures in Package Body

Posted on 2001-09-07
7
9,130 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
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
PRTG Network Monitor: Intuitive Network Monitoring

Network Monitoring is essential to ensure that computer systems and network devices are running. Use PRTG to monitor LANs, servers, websites, applications and devices, bandwidth, virtual environments, remote systems, IoT, and many more. PRTG is easy to set up & use.

 
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

PRTG Network Monitor: Intuitive Network Monitoring

Network Monitoring is essential to ensure that computer systems and network devices are running. Use PRTG to monitor LANs, servers, websites, applications and devices, bandwidth, virtual environments, remote systems, IoT, and many more. PRTG is easy to set up & use.

Join & Write a Comment

Suggested Solutions

Title # Comments Views Activity
how to tune this sql query 61 101
Create index on View 27 52
Oracle regular expression 6 30
report returning null 21 50
Truncate is a DDL Command where as Delete is a DML Command. Both will delete data from table, but what is the difference between these below statements truncate table <table_name> ?? delete from <table_name> ?? The first command cannot be …
Note: this article covers simple compression. Oracle introduced in version 11g release 2 a new feature called Advanced Compression which is not covered here. General principle of Oracle compression Oracle compression is a way of reducing the d…
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 recover a database from a user managed backup

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

Need Help in Real-Time?

Connect with top rated Experts

14 Experts available now in Live!

Get 1:1 Help Now