List all procedures from a schema of Oracle database

How to list all procedures from a schema of Oracle database

To list all procedures from a schema of Oracle database you can query views: USER_PROCEDURES, ALL_PROCEDURES, DBA_PROCEDURES, USER_OBJECTS.

USER_PROCEDURES view contains information about stored procedures that are owned by the current user. The view has several columns that provide information such as the procedure name, the schema it belongs to, and its creation and last modification dates. By querying this view, a user can retrieve information about all the stored procedures they own in the current database.

SELECT procedure_name FROM USER_PROCEDURES;

ALL_PROCEDURES view contains information about stored procedures that are accessible by the current user. This includes procedures that the user has access to through their own schema, as well as procedures that have been granted to the user or to public. The view has several columns that provide information such as the procedure name, the schema it belongs to, and its creation and last modification dates.

SELECT procedure_name FROM ALL_PROCEDURES;

DBA_PROCEDURES view contains information about all stored procedures in the database, regardless of the owner. This view can only be accessed by users with the DBA role. It has several columns that provide information such as the procedure name, the schema it belongs to, and its creation and last modification dates.

SELECT procedure_name FROM DBA_PROCEDURES;

Examples

  
SELECT * FROM USER_PROCEDURES ;

SELECT * FROM USER_PROCEDURES 
WHERE OBJECT_TYPE='PROCEDURE';

SELECT * FROM USER_PROCEDURES 
WHERE OBJECT_TYPE='PACKAGE' 
AND PROCEDURE_NAME IS NOT NULL;

SELECT * FROM ALL_PROCEDURES 
WHERE OBJECT_TYPE='PROCEDURE';

SELECT * FROM ALL_PROCEDURES 
WHERE OBJECT_TYPE='PACKAGE' 
AND PROCEDURE_NAME IS NOT NULL;

SELECT * FROM DBA_PROCEDURES 
WHERE OBJECT_TYPE='PROCEDURE';

SELECT * FROM DBA_PROCEDURES 
WHERE OBJECT_TYPE='PACKAGE' 
AND PROCEDURE_NAME IS NOT NULL;

SELECT * FROM USER_OBJECTS 
WHERE OBJECT_TYPE = 'PROCEDURE';