9.8. Compilation Dependency

Concerning just packages, a specification must exist and compile without errors before the package body can compile.

9.8.1. Scenario 1

The following is a package specification and body. There is one procedure.

-- Filename PK_SPECIFICATION.SQL
CREATE OR REPLACE PACKAGE pk is
    PROCEDURE p1;
END pk;
/

-- Filename PK_BODY.SQL
CREATE OR REPLACE PACKAGE BODY pk is
    PROCEDURE p1 IS
    begin
        dbms_output.put_line('execute p1'),
    END p1;
END pk;
/

Figure 9-5 illustrates the package PK and the visible procedure P1. Additionally, there is a second package, AX. An AX procedure uses the procedure P1.

The body of AX is dependent on the specification of PK. Specifically, the body of the procedure A1 calls PK.P1.

The body of AK becomes invalid if we recompile the PK specification. The body of AK becomes invalid if we recompile the AK specification—recompiling a package specification always invalidates the body.

Recompiling the PK body or AX body has no impact to other code. This is always the case. For any package, recompiling the package body is an isolated event that will not affect any other stored procedure.

Recompiling a package specification will invalidate the associated package body. It may also affect the bodies of other packages. This would be the case when the body code calls procedures in the recompiled spec.

The following summarizes the package compilation order.

• Recompiling PK specification forces:

  • Recompiling PK body and AK body.

• Recompiling AK specification forces:

  • Recompiling AK body.

• Recompiling either PK body or AK body affects nothing.

9.8.2. Scenario 2

Two packages can have their body dependent on each other's package specification. The following illustrates this case.

The first package is PK. The second package is AX. A PK procedure references an AK procedure. An AX procedure references a PK procedure. See Figure 9-6.

• PK.print_something_else calls AX.print_square_of_2

• AX.print_something_else calls PK.print_time

To compile the four program units, two specs and two bodies, the package specifications must be compiled first, then the bodies. The code for this shows the package specifications first, then the bodies.

CREATE OR REPLACE PACKAGE pk is
    PROCEDURE print_time;
    PROCEDURE print_something_else;
END pk;
/

CREATE OR REPLACE PACKAGE ax is
    PROCEDURE print_square_of_2;
    PROCEDURE print_something_else;
END ax;
/

CREATE OR REPLACE PACKAGE BODY pk is
    PROCEDURE print_time IS
    begin
        dbms_output.put_line(SYSDATE);
    END print_time;
    PROCEDURE print_something_else IS
    BEGIN
        ax.print_square_of_2;
    END print_something_else;
END pk;
/

CREATE OR REPLACE PACKAGE BODY ax is
    PROCEDURE print_square_of_2 IS
    begin
        dbms_output.put_line(SQRT(2));
    END print_square_of_2;

    PROCEDURE print_something_else IS
    BEGIN
        pk.print_time;
    END print_something_else;
END ax;
/

Recompiling has the following effects.

• Recompiling PK specification forces:

  • Recompiling PK body and AK body.

• Recompiling AK specification forces:

  • Recompiling AK body and PK body.

• Recompiling either PK body or AK body affects nothing.

Recompiling a body affects nothing, but recompiling a specification could have wide implications by invalidating the body of that package plus many other package bodies.