Check if oracle function exists

Question

I have an Oracle schema where I have some functions and some packages that have functions inside.

How to check if a string value is a function name in my schema, and if so, return the result of the function or if it is not a function to return the string value?

I tried to start with

SELECT * FROM ALL_OBJECTS WHERE OBJECT_TYPE IN ('FUNCTION','PROCEDURE','PACKAGE','PACKAGE_BODY') 

but this does not return function names inside packages. Thanks!

Answer 1

Use the ALL_PROCEDURES view .

The OBJECT_NAME column will show the name of the autonomous functions and procedures and the package names. The PROCEDURE_NAME column will show you the names of the functions and procedures inside the package.

Since you are only interested in functions, you might be better off using ALL_ARGUMENTS . This view records the parameters used by the procedure or function. If this program block has a POSITION with a value of 0, this indicates the return value for the function.