Testing to see if a procedure exists within a package body

[tsyle]

I'm not sure if this question goes here, but i couldn't find a development forum for PL/SQL. I am using Oracle 9 and I have some procedures that are stored in a package body. I want to test to see if a procedure exists. Does anyone know if this is possible?

 

[KenCunningham]

select object_type, object_name
from user_objects (or dba_objects)
where object_type = 'PROCEDURE'
or object_type = 'FUNCTION'
and object_name = '<NAME>'

 

[LKBrwnDBA]

DESC MyPackage

----------------------------------------------------------------------------
The person who says it can't be done should not interrupt the person doing it. -- Chinese proverb

 

[tsyle]

I tried doing it that way but it doesn't work. My procedures are all stored within a package body. I looked inside the database and i think the only way i can query what is inside the package is by doing a search of the source code... I'm not that familar with pl/sql so i was hoping there might be another way to do it.

 

[LKBrwnDBA]

try in sqlplus:

SQL>desc sys.utl_file
PROCEDURE FCLOSE
 Argument Name                  Type                    In/Out Default?
 ------------------------------ ----------------------- ------ --------
 FILE                           RECORD                  IN/OUT
   ID                           BINARY_INTEGER          IN/OUT
PROCEDURE FCLOSE_ALL
PROCEDURE FFLUSH
 Argument Name                  Type                    In/Out Default?
 ------------------------------ ----------------------- ------ --------
 FILE                           RECORD                  IN
   ID                           BINARY_INTEGER          IN
FUNCTION FOPEN RETURNS RECORD
 Argument Name                  Type                    In/Out Default?
 ------------------------------ ----------------------- ------ --------
   ID                           BINARY_INTEGER          OUT
 LOCATION                       VARCHAR2                IN
 FILENAME                       VARCHAR2                IN
 OPEN_MODE                      VARCHAR2                IN
FUNCTION FOPEN RETURNS RECORD
 Argument Name                  Type                    In/Out Default?
 ------------------------------ ----------------------- ------ --------
   ID                           BINARY_INTEGER          OUT
 LOCATION                       VARCHAR2                IN
 FILENAME                       VARCHAR2                IN
 OPEN_MODE                      VARCHAR2                IN
 MAX_LINESIZE                   BINARY_INTEGER          IN
PROCEDURE GET_LINE
 Argument Name                  Type                    In/Out Default?
 ------------------------------ ----------------------- ------ --------
 FILE                           RECORD                  IN
   ID                           BINARY_INTEGER          IN
 BUFFER                         VARCHAR2                OUT
FUNCTION IS_OPEN RETURNS BOOLEAN
 Argument Name                  Type                    In/Out Default?
 ------------------------------ ----------------------- ------ --------
 FILE                           RECORD                  IN
   ID                           BINARY_INTEGER          IN
PROCEDURE NEW_LINE
 Argument Name                  Type                    In/Out Default?
 ------------------------------ ----------------------- ------ --------
 FILE                           RECORD                  IN
   ID                           BINARY_INTEGER          IN
 LINES                          BINARY_INTEGER          IN     DEFAULT
PROCEDURE PUT
 Argument Name                  Type                    In/Out Default?
 ------------------------------ ----------------------- ------ --------
 FILE                           RECORD                  IN
   ID                           BINARY_INTEGER          IN
 BUFFER                         VARCHAR2                IN
PROCEDURE PUTF
 Argument Name                  Type                    In/Out Default?
 ------------------------------ ----------------------- ------ --------
 FILE                           RECORD                  IN
   ID                           BINARY_INTEGER          IN
 FORMAT                         VARCHAR2                IN
 ARG1                           VARCHAR2                IN     DEFAULT
 ARG2                           VARCHAR2                IN     DEFAULT
 ARG3                           VARCHAR2                IN     DEFAULT
 ARG4                           VARCHAR2                IN     DEFAULT
 ARG5                           VARCHAR2                IN     DEFAULT
PROCEDURE PUT_LINE
 Argument Name                  Type                    In/Out Default?
 ------------------------------ ----------------------- ------ --------
 FILE                           RECORD                  IN
   ID                           BINARY_INTEGER          IN
 BUFFER                         VARCHAR2                IN

----------------------------------------------------------------------------
The person who says it can't be done should not interrupt the person doing it. -- Chinese proverb

 

[slaing]

Hi tsyle,

If the above hasn't solved your problem then could you elaborate on what you're trying to do. eg

1) are you trying to find these procedures yourself, (say, sitting in front of a SQL*Plus session), or are you trying to write some PL/SQL to programmatically look inside packages for these functions/procs ?

2) are these publicly declared functions/procs ? ie are they all going to be in the package headers ?


Steve

 

[tsyle]

I actually found another way to solve the problem. Thank you for all your help.

 

[carp]

If you will post the solution you found, then the next person with this problem will be able to get the answer more quickly.