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.
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 ]
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.
Copyright (c) 2000 O'Reilly & Associates. All rights reserved.