How to get list of all the procedure inside a package oracle

oracle plsql data-dictionary
शेखर picture शेखर · Dec 28, 2011 · Viewed 81.6k times · Source

Can I get the name of all the function inside a package. Suppose I have a package PKG_OWA and I want to list all the procedure inside the package.

Answer

APC picture APC · Dec 28, 2011

The data dictionary view ALL_PROCEDURES (or USER_PROCEDURES if you just want your packages). Find out more.

 select procedure_name
 from all_procedures
 where owner = 'YOU'
 and object_name = 'YOUR_PACKAGE'

This lists the public procedures exposed in the package specification. There is no easy way of retrieving the private procedures (that is, those specified only in the package body) except by processing the source text. Oracle do provide a utility PL/SCOPE which we can use to gather this information, but it requires us to change session settings and recompile our code, so it may not be suitable in all situations. Find out more.

Related questions

SQL error "ORA-01722: invalid number"

A very easy one for someone, The following insert is giving me the ORA-01722: invalid number why? INSERT INTO CUSTOMER VALUES (1,'MALADY','Claire','27 Smith St Caulfield','0419 853 694'); INSERT INTO CUSTOMER VALUES (2,'GIBSON','Jake','27 Smith St Caulfield','0415 713 598…

Read More
Is there a combination of "LIKE" and "IN" in SQL?

In SQL I (sadly) often have to use "LIKE" conditions due to databases that violate nearly every rule of normalization. I can't change that right now. But that's irrelevant to the question. Further, I often use conditions like WHERE something …

Read More
ORA-06502: PL/SQL: numeric or value error: character string buffer too small

I tried the following code different ways, like by taking out the while or the if, but when I put both together (if and while), I always get the error at the end... undefine numero set serveroutput on accept numero …

Read More