Managing Code in the Database - Oracle

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 and exposed through a wide range of data dictionary views. This is a tremendous advantage for two key reasons:

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

I can write queries and even entire PL/SQL programs to read the contents of these data dictionary views and obtain lots of fascinating and useful information about my code base.

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.

Data Dictionary Views for PL/SQL Programmers

The Oracle data dictionary is a jungle—lushly full of incredible information, but often with less than clear pathways to your destination. There are hundreds of views built on hundreds of tables, many complex interrelationships, special codes, and, all too often, nonoptimized view definitions. A subset of this multitude is particularly handy to PL/SQL developers; we will take a closer look at the key views in a moment. First, it is important to know that 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). Generally they have the same columns as the corresponding USER view, with the addition of an OWNER column in the ALL views.

DBA_*

Views that show information about all the objects in the database. Generally the same columns as the corresponding ALL view.

We will work with the USER views in this chapter; you can easily modify any scripts and techniques to work with the ALL views by adding an OWNER column to your logic. The following are some views a PL/SQL developer is most likely to find useful:

USER_DEPENDENCIES

The dependencies to and from objects you own. This view is mostly used by Oracle to mark objects INVALID when necessary, and also by IDEs to display the dependency information in their object browsers.

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. You can, however, write your own queries against it as well.

USER_OBJECTS

The objects you own. You can, for instance, use this view to see if an object is marked INVALID, find all the packages that have “EMP” in their names, etc.

USER_OBJECT_SIZE

The size of the objects you own. Actually, this view will show you the source, parsed, and compile sizes for your code. Use it to identify the large programs in your environment, good candidates for pinning into the SGA.

USER_PLSQL_OBJECT_SETTINGS

(Introduced in Oracle Database 10g Release 1) Information about the characteristics of a PL/SQL object that can be modified through the ALTER and SET DDL commands, such as the optimization level, debug settings, and more.

USER_PROCEDURES

(Introduced in Oracle9i Database Release 1) Information about stored programs, such as the AUTHID setting, whether the program was defined as DETERMINISTIC, and so on.

USER_SOURCE

The text source code for all objects you own (in Oracle9i Database and above, including database triggers and Java source). This is a very handy view, because you can run all sorts of analysis of the source code against it using SQL and, in particular, Oracle Text.

USER_TRIGGERS and USER_TRIG_COLUMNS

The database triggers you own, and any columns identified with the triggers. You can write programs against this view to enable or disable triggers for a particular table.

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 a 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.