I am using the Oracle system tables to get some metadata about the Packages in our database.
Here is one of my queries:
select AP.PROCEDURE_NAME
from ALL_PROCEDURES ap
where ap.object_name = :object_name
and ap.owner=:owner
and ap.procedure_name is not NULL
and ap.procedure_name like :procedure_name
I also want to find out if the given object is either a function, a procedure or a type. I can't seem to find a direct way to pull the data directly from a table.
Please Note: I don't want to have to parse it from all_sources.
It turns out that there is a view all_arguments where you can get all the parameters for a method in a package. When the method is a function there is an argument with a null name representing the return value. So if you join against all_arguments you can determine if a given all_procedures entry is a function or a procedure. What follows is an example showing such a select.