Dependency Chain (PKG vs Proc)

Not something entirely learned new today - but rather a revisited "something I learned".

Nice quote from Oracle product documentation about dependency chain
If the body of the referenced package is replaced, then the dependent procedure is not affected. However, if the specification of the referenced package is replaced, then the dependent procedure is invalidated. This is a mechanism for minimizing dependencies among procedures and referenced objects by using packages.


If procedure A calls procedure B and B is "changed", then A is directly affected and must be recompiled.  There is no "auto recompile A when B changes" option -- although A will recompile itself automatically the next time it is run.
    On the other hand, If I create a package PKG_A with a procedure A and a package PKG_B with a procedure B then PKG_A will be dependent on PKG_B's specification.  I can compile and recompile PKG_B's Body as often as I like without affecting PKG_A's state.  As long as PKG_B's specification or interface does not change -- dependent objects are not affected by the recompilation of the body.

test@10gR2> create or replace procedure B
  2  as
  3  begin
  4     null;
  5  end;
  6  /

Procedure created.

test@10gR2> create or replace procedure A
  2  as
  3  begin
  4     b;
  5  end;
  6  /

Procedure created.

So procedure A calls B.  One of the downsides to procedures, in addition to the direct dependency, is that you have to create them in the "right" order if you want everything valid in the database after an install.  I had to create B before A.  Packages don't have this nuance either -- as shown below

test@10gR2> create or replace package pkg_a as
  2     procedure a;
  3  end pkg_a;
  4  /

Package created.

test@10gR2> create or replace package pkg_b as
  2     procedure b;
  3  end pkg_b;
  4  /

Package created.

Here i have created the package specification, they are not dependent on other and can be created in any order.

test@10gR2> create or replace package body pkg_a as
  2     procedure a
  3     is
  4     begin
  5             pkg_b.b;
  6     end a;
  7  end pkg_a;
  8  /

Package body created.

test@10gR2> create or replace package body pkg_b as
  2     procedure b
  3     is
  4     begin
  5             null;
  6     end b;
  7  end pkg_b;
  8  /

Package body created.

Then i created the bodied - I can create bodies in any order after the specification (In general) -  PKG_A is dependent on Pkg_B specification - not is body. so we don't have the ordering problem.

test@10gR2> select object_type,object_name,status
  2  from user_objects
  3  where status ='INVALID';

no rows selected

nothing is Invalid.

test@10gR2> alter procedure b compile;

Procedure altered.

test@10gR2> select object_type,object_name,status
  2  from user_objects
  3  where status ='INVALID';

OBJECT_TYPE         OBJECT_NAME                    STATUS
------------------- ------------------------------ -------
PROCEDURE           A                              INVALID

Standalone procedure A goes invalid, B was changed and A needs to be recompiled.

test@10gR2> alter package pkg_b compile body;

Package body altered.

test@10gR2> select object_type,object_name,status
  2  from user_objects
  3  where status ='INVALID';

OBJECT_TYPE         OBJECT_NAME                    STATUS
------------------- ------------------------------ -------
PROCEDURE           A                              INVALID

Pkg_A never goes invalid - As long as spec does not change, Pkg_A remains valid.

Don't use standalone procedures!  Use packages.  Packages break the dependency chain.