[ Team LiB ] |
9.4 Dependencies among ProceduresThis section covers the following topics:
When we first compile the HELLO procedure, the CREATED time and LAST_DDL_TIME are identical.
If we attempt to recompile the procedure and the compile fails, the procedure is still in the data dictionary but with an INVALID status. The LAST_DDL_TIME reflects the last compile time. Executing a procedure that is INVALID will fail with an Oracle error:
If Procedure A calls Procedure B and B becomes invalid, then A automatically becomes invalid. For the Figure 9-2 procedure, SAY_HELLO calls HELLO. What happens if HELLO becomes invalid? Figure 9-2. Simple Procedure Dependency.We begin with the code to HELLO.SQL and SAY_HELLO.SQL.
Compile procedures HELLO and SAY_HELLO in order. The SHOW ERRORS command reports any compilation errors. The script CHECK_PLSQL_OBJECTS shows the STATUS as VALID for each procedure in USER_OBJECTS.
Edit HELLO.SQL and change PUT_LINE to PUTLINE. The procedure will now compile with an error. Recompile HELLO with @HELLO.SQL. The status of SAY_HELLO is also invalid, yet we did not change the procedure. SAY_HELLO depends on a valid HELLO procedure. A compile error in Hello resulted in Oracle searching objects that depend on HELLO and invalidating those objects. All dependents of any procedure must be valid for that procedure to be valid, showing both objects as invalid:
Correct the PL/SQL code in HELLO.SQL and recompile. The HELLO should be valid with a successful recompilation. What about SAY_HELLO, is this still invalid?
Procedure SAY_HELLO is still invalid; however, when we execute the procedure, Oracle sees that it is invalid and attempts to validate it. This will be successful because all dependents (i.e., the HELLO procedure) are valid. Oracle compiles SAY_HELLO, sets the status to valid, and then executes the procedure. Following execution of HELLO, both procedures are valid.
There is understandable overhead with Oracle attempting to validate objects at run time. If HELLO is a widely used procedure and becomes invalid, there will be some performance degradation. During the normal operations of an application, Oracle may encounter many packages that became invalid and recompile them at run time. This can cause a noticeable impact to end users. The following discussion covers the scenario when invalid code does not recompile. We invalidated HELLO, recompiled it, and it became valid again. The change was a simple statement change that we corrected. A major code change to HELLO could cause recompilation failures in other procedures. Such an event would occur if the interface to HELLO changed. Changing the parameter specification, parameter types, or parameter modes can permanently invalidate other code. If we change a procedure and recompile, Oracle's recompilation of other procedures may fail. Why wait until run-time to realize there is broken code. When PL/SQL changes occur, you can recompile the entire suite of PL/SQL code in the schema. The Oracle DBMS_UTILITY package provides this functionality with the COMPILE_SCHAME procedure. To recompile all PL/SQL in a schema (this example uses the schema name, SCOTT):
The response "procedure successfully completed" means the call to DBMS_UTILITY was successful. There may be invalid objects. Run CHECK_PLSQL_OBJECTS for invalid stored procedures. LAST_DDL_TIME shows the recompilation time of each procedure. If a procedure is invalid, you can SHOW ERRORS on that procedure, showing why it failed to compile with the following:
To show compile errors for SAY_HELLO:
The following scenario includes three procedures. P1 calls P2, which calls P3. The procedure code is:
Compile these procedures in the following order: P3, then P2, then P1. Execution of P1 produces the following:
Change P3 by adding a parameter to the interface and compile the procedure.
Not knowing all the dependencies on P3, we can compile the schema.
Check for invalid objects.
We have invalid objects P1 and P2. Use SHOW ERRORS to see why these procedures failed to compile.
Many invalid objects can pose a challenging problem. In the preceding example (P1, P2 and P3), there are two invalid objects. We changed P3 and saw from SHOW ERRORS that P2 is passing the wrong number of arguments to P3. The DBMS_UTILITY.COMPILE_SCHEMA compiles all the PL/SQL in a schema. You can validate individual components with the ALTER statement.
To compile the package specification and body:
To compile just the package specification:
To compile just the package body:
You can always determine object dependencies by querying the data dictionary view USER_DEPENDENCIES, covered in the next section. The preceding scenario includes three procedures: P1, P2, and P3. This is not a complex architecture. When there are many program units with many dependencies, the task becomes tedious. It requires repeated queries of the USER_DEPENDENCIES view. In the next section, we look at applying a general solution to querying USER_DEPENDENCIES. The script used to query the USER_OBJECTS view, CHECK_PLSQL_OBJECTS, filters stored procedures with a WHERE clause. This script filters stored procedures just to demonstrate an example. A change to a stored procedure can invalidate other object types. A view can use a PL/SQL function. A trigger can use a procedure, function, or package. Objects from other schemas may use our PL/SQL objects. A general dependency tracing strategy requires that you query the ALL_DEPENDENCIES view for all object types. |
[ Team LiB ] |
No comments:
Post a Comment