9.4 Dependencies among Procedures

This section covers the following topics:

• Editing and compiling a procedure that invalidates other procedures.

• LAST_DDL_TIME and STATUS from USER_OBJECTS.

• Compiling a schema with DBMS_UTILITY.COMPILE_SCHEMA.

• Recompiling procedures, functions, and packages individually.

• This section uses the script CHECK_PLSQL_OBJECTS from the previous section.

When we first compile the HELLO procedure, the CREATED time and LAST_DDL_TIME are identical.

 OBJECT_NAME    STATUS  CREATED           LAST_DDL_TIME -------------- ------- ----------------- ----------------- HELLO(P)       VALID   14-jul-2003 16:18 14-jul-2003 16:18 

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:

 PLS-00905: object SCOTT.HELLO is invalid 

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.

 -- Filename HELLO.SQL CREATE OR REPLACE PROCEDURE hello IS BEGIN     dbms_output.put_line('Hello'); END; / show errors -- Filename SAY_HELLO.SQL CREATE OR REPLACE PROCEDURE say_hello IS BEGIN     hello; END; / show errors 

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.

  SQL>  @CHECK_PLSQL_OBJECTS  OBJECT_NAME          STATUS  CREATED      LAST_DDL_TIM   -------------------- ------- ------------ ------------   HELLO(P)             VALID   25-jul 12:52 25-jul 01:02   SAY_HELLO(P)         VALID   25-jul 01:01 25-jul 01:02  

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:

  SQL>  @CHECK_PLSQL_OBJECTS  OBJECT_NAME          STATUS  CREATED      LAST_DDL_TIM   -------------------- ------- ------------ ------------   HELLO(P)             INVALID 25-jul 12:52 25-jul 01:05   SAY_HELLO(P)         INVALID 25-jul 01:01 25-jul 01:02  

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?

  SQL>  @hello  Procedure created.   SQL>  @CHECK_PLSQL_OBJECTS  OBJECT_NAME          STATUS  CREATED      LAST_DDL_TIM   -------------------- ------- ------------ ------------   HELLO(P)             VALID   25-jul 12:52 25-jul 01:17   SAY_HELLO(P)         INVALID 25-jul 01:01 25-jul 01:02  

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.

  SQL>  execute say_hello  Hello   PL/SQL procedure successfully completed.   SQL>  @CHECK_PLSQL_OBJECTS  OBJECT_NAME          STATUS  CREATED      LAST_DDL_TIM   -------------------- ------- ------------ ------------   HELLO(P)             VALID   25-jul 12:52 25-jul 01:17   SAY_HELLO(P)         VALID   25-jul 01:01 25-jul 01:17  

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):

  SQL>  execute dbms_utility.compile_schema('SCOTT')  PL/SQL procedure successfully completed.  

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:

• SHOW ERRORS <type> <schema>.<name>

• SHOW ERRORS PROCEDURE procedure_name;

• SHOW ERRORS FUNCTION function_name;

• SHOW ERRORS PACKAGE package_name; (package spec errors)

• SHOW ERRORS PACKAGE BODY package_name; (package body errors)

To show compile errors for SAY_HELLO:

 SHOW ERRORS PROCEDURE SAY_HELLO; 

The following scenario includes three procedures. P1 calls P2, which calls P3. The procedure code is:

 CREATE OR REPLACE procedure P3 IS BEGIN     dbms_output.put_line('executing p3'); END; / CREATE OR REPLACE procedure P2 IS BEGIN     P3; END; / CREATE OR REPLACE procedure P1 IS BEGIN     P2; END; / 

Compile these procedures in the following order: P3, then P2, then P1. Execution of P1 produces the following:

  SQL>  execute p1  executing p3  

Change P3 by adding a parameter to the interface and compile the procedure.

 CREATE OR REPLACE procedure P3(N INTEGER) IS BEGIN     dbms_output.put_line('executing p3'); END; / 

Not knowing all the dependencies on P3, we can compile the schema.

  SQL>  execute dbms_utility.compile_schema('SCOTT');  PL/SQL procedure successfully completed.  

Check for invalid objects.

  SQL>  @check_plsql_objects  OBJECT_NAME          STATUS  CREATED      last_ddl   -------------------- ------- ------------ ------------   P1(P)                INVALID 25-jul 15:26 25-jul 15:35   P2(P)                INVALID 25-jul 15:26 25-jul 15:35   P3(P)                VALID   25-jul 15:26 25-jul 15:35  

We have invalid objects P1 and P2. Use SHOW ERRORS to see why these procedures failed to compile.

  SQL>  show errors procedure p1  Errors for PROCEDURE P1:   LINE/COL ERROR   -------- ----------------------------------------------------   3/5      PLS-00905: object SCOTT.P2 is invalid   3/5      PL/SQL: Statement ignored   SQL>  show errors procedure p2  Errors for PROCEDURE P2:   LINE/COL ERROR   -------- -----------------------------------------------------   3/5      PLS-00306: wrong number or types of arguments   in call to 'P3'   3/5      PL/SQL: Statement ignored  

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.

 ALTER PROCEDURE  procedure_name  COMPILE; ALTER FUNCTION  function_name  COMPILE 

To compile the package specification and body:

 ALTER PACKAGE  package_name  COMPILE; 

To compile just the package specification:

 ALTER PACKAGE  package_name  COMPILE SPECIFICATION; 

To compile just the package body:

 ALTER PACKAGE  package_name  COMPILE 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.