mqateq
asked on
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???
is there any table (ALL_... or USER_...) available to query the list???
ASKER CERTIFIED SOLUTION
membership
This solution is only available to members.
To access this solution, you must be a member of Experts Exchange.
RAMESH,
DESCRIBE packagename will not work. In SQL*Plus, you can only DESCRIBE <packagename>.<procedure>
thomas
DESCRIBE packagename will not work. In SQL*Plus, you can only DESCRIBE <packagename>.<procedure>
thomas
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
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,'FUNCTIO N','PROCED URE')
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,'FUNCTIO N','PROCED URE')
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,'FUNCTIO N','PROCED URE')
FROM USER_ARGUMENTS
WHERE PACKAGE_NAME='YOUR-PACKAGE _NAME'
AND OWNER = 'PACKAGE-OWNER';
Solomon Yakobson.
SELECT DISTINCT OBJECT_NAME
FROM USER_ARGUMENTS
WHERE PACKAGE_NAME='YOUR-PACKAGE
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,'FUNCTIO
FROM USER_ARGUMENTS
WHERE PACKAGE_NAME='YOUR-PACKAGE
Note, that procedure overloaded with function will appear twice: as procedure and as function:
SQL> SELECT DISTINCT OBJECT_NAME,
2 DECODE(POSITION,0,'FUNCTIO
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
AND OWNER = 'PACKAGE-OWNER';
or
SELECT DISTINCT OBJECT_NAME,
DECODE(POSITION,0,'FUNCTIO
FROM USER_ARGUMENTS
WHERE PACKAGE_NAME='YOUR-PACKAGE
AND OWNER = 'PACKAGE-OWNER';
Solomon Yakobson.
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
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
Force accepted
** Mindphaser - Community Support Moderator **
ramkb, there will be a separate question with points for your help.
** Mindphaser - Community Support Moderator **
ramkb, there will be a separate question with points for your help.
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