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 ...

Get Oracle and PL/SQL Recipes: A Problem-Solution Approach now with the O’Reilly learning platform.

O’Reilly members experience books, live events, courses curated by job role, and more from O’Reilly and nearly 200 top publishers.