How can I check if a procedure exists in a package?

Question

I have packages with procedures, which are used in many places and in some places I need slightly different procedures, e.g. updating one more column.

I thought that I could create an extra package, which would contain some but not all of the procedures.

Is there any way to check in an if statement directly in scripts, that if there does not exist procedure in the extra package, to fall back on the standard package?

Answer 1

You should be able to obtain 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 row the procedure you're interested in exists in the package. If you get a NO_DATA_FOUND exception it means the procedure doesn't exist in the package.

Share and enjoy.