Finding Subprograms in the Oracle Data Dictionary

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.

Code Sample:

Introducing-Subprograms/Demos/query_all_objects_counts.sql

select object_type, count(*)
from all_objects
group by object_type
order by object_type;

Code Explanation

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.

Code Sample:

Introducing-Subprograms/Demos/query_user_procedures.sql

select * from user_procedures;

Code Explanation

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.

Code Sample:

Introducing-Subprograms/Demos/query_user_triggers.sql

select * from user_triggers;

Code Explanation

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.

Code Sample:

Introducing-Subprograms/Demos/query_user_source.sql

select * from user_source order by name, type, line;

Code Explanation

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.