Information about subprograms (including the actual source code) is stored in the Oracle Data Dictionary. If you would like general information about the Oracle Data Dictionary, the DICTIONARY view can provide information about each view, and the DICT_COLUMNS view has comments about individual columns in each view.
select object_type, count(*) from all_objects group by object_type order by object_type;
The ALL_OBJECTS view holds data on all objects available to the current user. The OBJECT_TYPE field indicates the type of object - in this course we are most interested in PACKAGE, PACKAGE BODY, PROCEDURE, FUNCTION, or TRIGGER. This SQL query returns a count of each object type that the user can access.
Remember, the data dictionary contains views that have the prefix of ALL_ to indicate objects owned by and accessible to the user, USER_ to indicate objects that are owned by the current user, and DBA_ to indicate objects anywhere in the database. Unless you are connected as the SYS or SYSTEM user, the DBA_ views require permission to use.
select * from user_procedures;
This SQL query returns the contents of the USER_PROCEDURES view. Although stored procedures are also listed in the ALL_OBJECTS view, this view contains columns that are specific to stored procedures.
select * from user_triggers;
Information about triggers, including the events that cause them to fire and the actual trigger source code are returned in this query against the USER_TRIGGERS view.
select * from user_source order by name, type, line;
The USER_SOURCE view contains the actual PL/SQL source code of objects compiled within the Oracle Database. The trigger source code available in the USER_TRIGGERS view is also available here. Each row in the view corresponds to a line of source code. This view can be used to search all PL/SQL code that exists in a given Oracle Database.
The following query does a case insensitive search on all lines of PL/SQL code that have the string "add" in them.