How to check if a procedure exists in a package?

Question

I have packages with procedures that are used in many places, and in some places I need slightly different procedures, for example. update another column.

I thought I could create an additional package that would contain some, but not all, procedures.

Is there a way to test the if directly in the scripts so that if there is no procedure in the add-on package to return to standard packages?

Answer 1

You can get this information from the DBA_PROCEDURES view:

 SELECT * FROM SYS.DBA_PROCEDURES WHERE OBJECT_TYPE = 'PACKAGE' AND OBJECT_NAME = '<your package name>' AND PROCEDURE_NAME = '<your procedure name>' 

If this returns a string, the procedure you are interested in exists in the package. If you get a NO_DATA_FOUND exception, it means that the procedure does not exist in the package.

Share and enjoy.

Answer 2

Does the user who requests SYS.DBA_PROCEDURES need special privileges? Perhaps less permissions are required for SYS.User_Objects queries?

 select * from SYS.User_Objects where object_type = 'PACKAGE'; 

Answer 3

You can also try

 SELECT text FROM all_source WHERE name = 'PACKAGE' ORDER BY line; 

Worked for me ...

Answer 4

 select * from USER_OBJECT where object_type='PACKAGE' AND OBJECT_NAME ='<YOUR PACKAGE NAME>'