Managing and Analyzing Code in the Database

When you CREATE OR REPLACE a PL/SQL program, the source code for that program, along with other representations of that software, is stored in the database itself. This is a tremendous advantage for two key reasons:

Information about that code is available to you via the SQL language

I can run a query against a data dictionary view that shows me all the programs that have been modified by a certain date, or tells me which programs are invalid and need to be recompiled.

The database manages dependencies between your stored objects

For example, if a stored function relies on a certain table, and that table's structure is changed, the status of that function is automatically set to INVALID. Recompilation then takes place automatically when someone tries to execute that function.

This SQL interface to your code base allows you to manage your code repository—running analyses on your code, documenting what has been written and changed, and so on. The following sections introduce you to some of the most commonly accessed sources of information in the data dictionary.

19.1.1 Data Dictionary Views for PL/SQL Programmers

The Oracle data dictionary is a jungle! There are hundreds of views built on hundreds of tables, many complex interrelationships, special codes, and, all too often, non-optimized view definitions. In general, there are three types or levels of data dictionary views:

USER_*

Views that show information about the database objects owned by the currently connected schema.

ALL_*

Views that show information about all of the database objects to which the currently connected schema has access (either because it owns them or because it has been granted access to them).

DBA_*

Views that show information about all the objects in the database.

Because the stored objects are contained in tables in the data dictionary, you can use SQL itself to get information about the currently available programs. The following views are the most useful to understand:

USER_DEPENDENCIES

The dependencies to and from objects you own.

USER_ERRORS

The current set of errors for all stored objects you own. This view is accessed by the SHOW ERRORS SQL*Plus command, described in Chapter 2.

USER_OBJECTS

The objects you own.

USER_OBJECT_SIZE

The size of the objects you own.

USER_SOURCE

The text source code for all objects you own.

USER_TRIGGERS

The database triggers you own.

USER_ARGUMENTS

The arguments (parameters) in all the procedures and functions in your schema.

You can view the structures of each of these views either with aDESC (describe) command in SQL*Plus or by referring to the appropriate Oracle documentation. The following sections provide some examples of the ways you can use these views.