How to List All Stored Procedures in Oracle Database

There are several data dictionary views that we can use to return a list of stored procedures in Oracle Database.

The `user_procedures` View

The user_procedures view lists all functions and procedures that are owned by the current user, along with their associated properties.

We can run a query against this view and filter its results to just stored procedures:

SELECT 
    object_name
FROM 
    user_procedures
WHERE
    object_type = 'PROCEDURE';

As mentioned, this returns only those procedures that are owned by the current user.

To return more than that, use one of the following views.

The all_procedures view lists all functions and procedures that are accessible to the current user, along with associated properties:

SELECT 
    owner,
    object_name
FROM 
    all_procedures
WHERE
    object_type = 'PROCEDURE';

This view includes an owner column that tells us who the owner is, so I’ve included that in the query here.

The dba_procedures view lists all functions and procedures that are available in the database, along with their associated properties.

SELECT 
    owner,
    object_name
FROM 
    dba_procedures
WHERE
    object_type = 'PROCEDURE';

This view’s columns are the same as those in the all_procedures view.