Is there a way to quickly list all the current user-owned functions in Oracle?
Something along the same lines as this, which lists the current user's tables
select * from user_tables;
-
nice idea to add the information-schema tag (I keep forgetting about information-schema, shows my oracle background. )– Niall LitchfieldNov 21, 2011 at 16:53
-
@Niall thanks, I felt weird about adding it, but a rose is still a rose... :)– Jack DouglasNov 22, 2011 at 11:42
Add a comment
|
2 Answers
The answer you are probably looking for is
select * from user_objects where object_type = 'FUNCTION';
However this won't return a list of packaged functions. I'm not aware of anyway of returning these other than by querying user_objects for objects of type 'PACKAGE' and then describing each in turn. Someone with a developer hat on might be able to answer the packaged function part of the question.
-
-
-
Hi Gaius, wouldn't you have to parse user_source for the text inside the package bodies. that sounds pretty horrible to me. Dec 8, 2011 at 10:13
-
DBA_PROCEDURES (will list all standalone as well as public procedures/functions etc in side a package) or DBA_ARGUMENTS can list procedures/functions within a package as well. In DBA_ARGUMENTS it is not easy to distinguish between procedures and functions. But functions have a position=0 argument and datatype shows rturn value. Procedures DO NOT have a position=0 argument. You can replace ALL_ for DBA_ if you don't have sufficient privileges.– RajMar 3, 2015 at 13:27
You can list any user owned objects by querying the user_objects table.
To do this for functions:
select object_name from user_objects where object_type = 'FUNCTION';
This table can also show other objects, like procedures and packages.
The user_objects table is supplemented by the table all_objects, which contains all the objects the current user has access to (i.e. grants to tables in other schemas etc).
The object_types this can be queried for on both tables is as follows:
select distinct object_type from all_objects;
OBJECT_TYPE
-------------------
JOB CLASS
INDEXTYPE
PROCEDURE
JAVA CLASS
SCHEDULE
WINDOW
WINDOW GROUP
JAVA RESOURCE
TABLE
TYPE
VIEW
FUNCTION
PROGRAM
SYNONYM
CONSUMER GROUP
EVALUATION CONTEXT
OPERATOR
PACKAGE
SEQUENCE
XML SCHEMA