#

| Chapter 23 Managing Code in the Database |

## 23.3 Module Validation and Dependency Management

Whenever you create or replace a stored object in the database, the PL/SQL engine compiles the code. If the compile succeeds, then the following information is stored in the database:

- The source code for the module, as you wrote it.

- The tree structure of the module, a hierarchical representation of the original source code, with a list of local and external references. The tree structure is used only by the compilation (and automatic recompilation) process.

- The pcode for the module, which is a list of instructions to the PL/SQL execution engine. The PL/SQL execution engine uses the pcode to execute the module when it is called by a host program.

When a module compiles, its status is set to VALID. This status is maintained in the SYS.OBJ$ table. Upon compilation, the PL/SQL engine also has resolved all references to other database objects such as tables and other stored programs. Each of these references constitutes a dependency for that module. In other words, the validity of the module is dependent upon the validity of all objects on which it depends. All dependency information is stored in the SYS.DEPENDENCY$ table.

> NOTE: The tree structure, pcode, and dependency information is maintained only for named modules. Anonymous blocks and database triggers are compiled only when (and each time that) they are executed. The generated pcode for these objects is stored directly in the shared pool of the database instance for as long as they are used, and until they are erased from the System Global Area, using a least-recently-used algorithm.

> Starting with Oracle Server Release 7.3, triggers are compiled and their pcode stored in the database.

### 23.3.1 Interdependencies of Stored Objects

A stored object must be VALID in order for its pcode to be loaded into the shared pool and executed by the host program. As noted above, if the compile succeeds at create/replace time, then the status is set to VALID. This status may, however, depend on other objects. Consider the following function:

>     FUNCTION full_name (employee_id_in IN NUMBER) RETURN VARCHAR2
>     IS
>        first_and_last VARCHAR2(100);
>     BEGIN
>        SELECT first_name || ' ' || last_name
>          INTO first_and_last
>          FROM employee
>         WHERE employee_id = employee_id_in;
>        RETURN first_and_last;
>     EXCEPTION
>        WHEN NO_DATA_FOUND
>        THEN
>           RETURN NULL;
>     END;

Suppose that on Monday I save this function to the database. It compiles successfully and its status is set to VALID. The PL/SQL compiler also adds a record in the DEPENDENCY$ table to indicate that full_name is dependent on the employee table. Then on Tuesday, the DBA team adds another column to the employee table. The Oracle Server automatically checks the dependencies for the employee table and sets the status of all dependent objects to INVALID. This is a recursive process. Once full_name is set to INVALID, then any modules calling full_name are also set to INVALID.[1]

> [1] An important exception to this "chain reaction" occurs with packaged modules. If, for example, full_name was defined within a package called, say, "employee", then even if the status of the full_name module is set to INVALID, no modules that call full_name will be tagged invalid, unless the specification of full_name changed (which it does not in this case). See the package examples and documentation on the disk for more information about this extra protection provided by packages.

The next time a user runs the full_name function, the database notices that the status is INVALID. It then calls the PL/SQL engine to compile the function. If the compile succeeds, then the pcode is loaded to shared memory and the function runs.

23.2 Transaction Integrity and Execute Authority |  | 23.4 Remote Procedure Calls

Copyright (c) 2000 O'Reilly & Associates. All rights reserved.