Finding Subprograms in the Oracle Data Dictionary

Contact Us or call 1-877-932-8228
Finding Subprograms in the Oracle Data Dictionary

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.

select * from user_source where lower(text) like '%add%';
Next