Oracle PL/SQL Programming, 2nd Edition

Oracle PL/SQL Programming, 2nd EditionSearch this book
Previous: 23.2 Transaction Integrity and Execute AuthorityChapter 23
Managing Code in the Database
Next: 23.4 Remote Procedure Calls

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:

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
   first_and_last VARCHAR2(100);
   SELECT first_name || ' ' || last_name
     INTO first_and_last
     FROM employee
    WHERE employee_id = employee_id_in;
   RETURN first_and_last;

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.

Previous: 23.2 Transaction Integrity and Execute AuthorityOracle PL/SQL Programming, 2nd EditionNext: 23.4 Remote Procedure Calls
23.2 Transaction Integrity and Execute AuthorityBook Index23.4 Remote Procedure Calls

The Oracle Library Navigation

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

Library Home Oracle PL/SQL Programming, 2nd. Ed. Guide to Oracle 8i Features Oracle Built-in Packages Advanced PL/SQL Programming with Packages Oracle Web Applications Oracle PL/SQL Language Pocket Reference Oracle PL/SQL Built-ins Pocket Reference
This HTML Help has been published using the chm2web software.