Solved

View list Of all procedures in Package Body

Posted on 2001-09-07
7
9,248 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.

Question has a verified solution.

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

Suggested Solutions

I remember the day when someone asked me to create a user for an application developement. The user should be able to create views and materialized views and, so, I used the following syntax: (CODE) This way, I guessed, I would ensure that useā€¦
When it comes to protecting Oracle Database servers and systems, there are a ton of myths out there. Here are the most common.
This video shows how to set up a shell script to accept a positional parameter when called, pass that to a SQL script, accept the output from the statement back and then manipulate it in the Shell.
This video shows how to copy an entire tablespace from one database to another database using Transportable Tablespace functionality.

856 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