What SQL would I need to use to list all the stored procedures on an Oracle database?

What SQL would I need to use to list all the stored procedures on an Oracle database?

If possible I’d like two queries:

1. list all stored procedures by name
2. list the code of a stored procedure, given a name

Answer

The DBA_OBJECTS view will list the procedures (as well as almost any other object):

SELECT owner, object_name
FROM dba_objects 
WHERE object_type = 'PROCEDURE'

The DBA_SOURCE view will list the lines of source code for a procedure in question:

SELECT line, text
FROM dba_source
WHERE owner = ?
  AND name = ?
  AND type = 'PROCEDURE'
ORDER BY line

Note: Depending on your privileges, you may not be able to query the DBA_OBJECTS and DBA_SOURCE views. In this case, you can use ALL_OBJECTS and ALL_SOURCE instead. The DBA_ views contain all objects in the database, whereas the ALL_ views contain only those objects that you may access.