4-19. Listing the Functions, Procedures, and Packages in a Schema
Problem
Your team has defined a number of functions, procedures, and packages within a schema. You want to generate a listing of all functions, procedures, and packages at the end of each day to evaluate productivity.
Solution
Use the USER_OBJECTS table to return the program list and prefix packages, procedures, and functions for the same program with the same first word to make them easier to find.
This first example will return a list of all procedure names that reside within the EMP schema and that have a name that is prefixed with EMPTIME:
SELECT OBJECT_NAME
FROM USER_OBJECTS
WHERE OBJECT_TYPE = 'PROCEDURE;
WHERE OBJECT_NAME like 'EMPTIME%';
The next query will return a list of all function names that reside within the schema:
SELECT OBJECT_NAME
FROM USER_OBJECTS
WHERE OBJECT_TYPE = 'FUNCTION';
Lastly, the following query will return a listing of all package names that reside within the schema:
SELECT OBJECT_NAME
FROM USER_OBJECTS
WHERE OBJECT_TYPE = 'PACKAGE';
How It Works
Oracle Database contains many views that contain data useful for application development. Using the USER_OBJECTS table can be very handy when searching for objects within the database. By prefixing like objects with the same first word, it can make searching for a particular selection of objects rather easy.
USER_OBJECTS provides the ability to find a certain object type by specifying the OBJECT_TYPE within the query. If no OBJECT_TYPE is specified, then all objects for the schema will be returned.