Oracle build order and PL/SQL package dependencies
Question: I'm trying to build up a list of PL/SQL package dependencies so that I can
help set up an automated build script for my packages to run on the test
server. Is there a way to start with a single package (a "root" package
identified by name, ideally) and then find all of the dependencies, and the
order they must be compiled in? Dependencies are already fully resolved in my
personal schema (so at least I have somewhere to start - but where do I go
next?).

(Oracle 10.2)

**EDIT:**

The build tool that is being used will use the build order and will retreive
those files in order from source control, and then pass them to Oracle to
compile (the actual build tool itself is written in Python or Java or both - I
don't have access to the source). Basically, the build tool needs as input a
list of files to compile _in the order they must be compiled in_ , and acces
to those files in source control. If it has that, everything will work quite
nicely.

**EDIT:**

Thanks for the neat scripts. Unfortunately, the build process is mostly out of
my hands. The process is based around a build tool which was built by the
vendor of the product we are integrating with, which is why the only inputs I
can give to the build process are a list of files in the order they need to be
built in. If there is a compiler error, the build tool fails, we have to
manually submit a request for a new build. So a list of files in the order
they should be compiled is important.

**EDIT:**

Found this:
<http://www.oracle.com/technology/oramag/code/tips2004/091304.html> Gives me
the dependencies of any object. Now I just need to get the ordering right...
If I get something working I'll post it here.

**EDIT:** (with code!)

I know that in general, this sort of thing is not necessary for Oracle, but
for anyone who's still interested...

I have cobbled together a little script that seems to be able to get a build
order such that all packages will be built in the correct order with no
dependency-related errors (with respect to pacakges) the first time around:

    
    
    declare
    
        type t_dep_list is table of varchar2(40) index by binary_integer;
        dep_list t_dep_list;
        i number := 1;
        cursor c_getObjDepsByNameAndType is
        --based on a query found here: http://www.oracle.com/technology/oramag/code/tips2004/091304.html
            select lvl, u.object_id, u.object_type, LPAD(' ', lvl) || object_name obj
            FROM (SELECT level lvl, object_id
                   FROM SYS.public_dependency s
                   START WITH s.object_id = (select object_id
                                             from user_objects
                                             where object_name = UPPER(:OBJECT_NAME)
                                                   and object_type = UPPER(:OBJECT_TYPE))
                   CONNECT BY s.object_id = PRIOR referenced_object_id
                   GROUP BY level, object_id) tree, user_objects u
            WHERE tree.object_id = u.object_id
                  and u.object_type like 'PACKAGE%' --only look at packages, not interested in other types of objects
            ORDER BY lvl desc;
    
        function fn_checkInList(in_name in varchar2) return boolean is
        begin
            for j in 1 .. dep_list.count loop
                if dep_list(j) = in_name then
                    return true;
                end if;
            end loop;
            return false;
        end;
    
    
    
        procedure sp_getDeps(in_objID in user_objects.object_id%type, in_name in varchar2) is
            cursor c_getObjDepsByID(in_objID in user_objects.object_id%type) is
            --based on a query found here: http://www.oracle.com/technology/oramag/code/tips2004/091304.html
                select lvl, u.object_id, u.object_type, LPAD(' ', lvl) || object_name obj
                FROM (SELECT level lvl, object_id
                       FROM SYS.public_dependency s
                       START WITH s.object_id = (select uo.object_id
                                                 from user_objects uo
                                                 where uo.object_name =
                                                       (select object_name from user_objects uo where uo.object_id = in_objID)
                                                       and uo.object_type = 'PACKAGE BODY')
                       CONNECT BY s.object_id = PRIOR referenced_object_id
                       GROUP BY level, object_id) tree, user_objects u
                WHERE tree.object_id = u.object_id
                      and u.object_id <> in_objID --exclude self (requested Object ID) from list.
                ORDER BY lvl desc;
        begin
            --loop through the dependencies
            for r in c_getObjDepsByID(in_objID) loop
                if fn_checkInList(trim(r.obj)) = false and (r.object_type = 'PACKAGE' or r.object_type = 'PACKAGE BODY') and
                   trim(r.obj) <> trim(in_name) then
                    dbms_output.put_line('checking deps of: ' || r.obj || ' ' || r.object_id || ' level: ' || r.lvl);
                    --now for each dependency, check the sub-dependency
                    sp_getDeps(r.object_id, trim(r.obj));
                    --add the object to the dependency list.
                    dep_list(i) := trim(r.obj);
                    i := i + 1;
                end if;
            end loop;
        exception
            when NO_DATA_FOUND then
                dbms_output.put_line('no more data for: ' || in_objID);
        end;
    
    begin
    
        for r in c_getObjDepsByNameAndType loop
            dbms_output.put_line('top-level checking deps of: ' || r.obj || ' ' || r.object_id || ' level: ' || r.lvl);
            sp_getDeps(r.object_id, trim(r.obj));
        end loop;
    
        dbms_output.put_line('dep count: ' || dep_list.count);
        for j in 1 .. dep_list.count loop
            dbms_output.put_line('obj: ' || j || ' ' || dep_list(j));
        end loop;
    end;
    

I know it's not the prettiest code (globals all over the place, etc... ugh),
and I'll probably repost it if I can get a chance this afternoon to clean it
up, but right now, it produces a build order that seems to run the first time
with no problems.

`:OBJECT_NAME` should be the root object that you want to trace all
dependencies and build order of. For me, this is a main package with a single
method that is the entry point to the rest of the system.

`:OBJECT_TYPE` I have mostly restricted to `PACKAGE BODY`, but it shouldn't be
too much work to include other types, such as triggers.

One last thing, the object specified by `:OBJECT_NAME` will not appear in the
output, but it should be the last item, so you'll have to add that to your
build list manually.

**UPDATE:** I just discovered `user_dependencies` and `all_dependencies`, this
code could probably be made much simpler now.


Answer: If you're really dealing with just PL/SQL packages you do not need to sweat
the build order. Just build all the package specifications first. Then you can
deploy all the package bodies and they will compile, because their
dependencies are the package specs.

If you happen to have some package specs which do depend on other specs - if
you have packages which declare, say, constants, subtypes or ref cursors which
are used in the signatures of packaged procedures - then you need to build
those package specs first. But there should be few enough of them that you can
arrange them in the build script by hand.

**edit**

> It looks like they wil be doing incremental AND "clean-sweep" builds, so the
> build order will matter most for when they clean out the environment and
> rebuild it.

That doesn't alter anything.

Here is an extended example. I have a schema with three packages....

    
    
    SQL> select object_name, object_type, status
      2  from user_objects
      3  order by 1, 2
      4  /
    
    OBJECT_NAME     OBJECT_TYPE     STATUS
    --------------- --------------- -------
    PKG1            PACKAGE         VALID
    PKG1            PACKAGE BODY    VALID
    PKG2            PACKAGE         VALID
    PKG2            PACKAGE BODY    VALID
    PKG3            PACKAGE         VALID
    PKG3            PACKAGE BODY    VALID
    
    6 rows selected.
    
    SQL>
    

The interesting thing is that a procedure in PKG1 calls a procedure from PKG2,
a procedure in PKG2 calls a procedure from PKG3 and a procedure in PKG3 calls
a procedure from PKG1.

**Q.** How does that circular dependency work?  
**A.** It's not a circular dependency....

    
    
    SQL> select name, type, referenced_name, referenced_type
      2  from user_dependencies
      3  where referenced_owner = user
      4  /
    
    NAME            TYPE            REFERENCED_NAME REFERENCED_TYPE
    --------------- --------------- --------------- ---------------
    PKG1            PACKAGE BODY    PKG1            PACKAGE
    PKG1            PACKAGE BODY    PKG2            PACKAGE
    PKG2            PACKAGE BODY    PKG2            PACKAGE
    PKG2            PACKAGE BODY    PKG3            PACKAGE
    PKG3            PACKAGE BODY    PKG3            PACKAGE
    PKG3            PACKAGE BODY    PKG1            PACKAGE
    
    6 rows selected.
    
    SQL> 
    

All the dependent objects are the package bodies, all the referenced objects
are the packaged specs. Consequently, if I trash'n'rebuild the schema it
really doesn't matter what order I use. First we trash ...

    
    
    SQL> drop package pkg1
      2  /
    
    Package dropped.
    
    SQL> drop package pkg2
      2  /
    
    Package dropped.
    
    SQL> drop package pkg3
      2  /
    
    Package dropped.
    
    SQL>
    

Then we rebuild ...

    
    
    SQL> create or replace package pkg3 is
      2      procedure p5;
      3      procedure p6;
      4  end pkg3;
      5  /
    
    Package created.
    
    SQL> create or replace package pkg2 is
      2      procedure p3;
      3      procedure p4;
      4  end pkg2;
      5  /
    
    Package created.
    
    SQL> create or replace package pkg1 is
      2      procedure p1;
      3      procedure p2;
      4  end pkg1;
      5  /
    
    Package created.
    
    SQL> create or replace package body pkg2 is
      2      procedure p3 is
      3      begin
      4          pkg3.p5;
      5      end p3;
      6      procedure p4 is
      7      begin
      8          dbms_output.put_line('PKG2.P4');
      9      end p4;
     10  end pkg2;
     11  /
    
    Package body created.
    
    SQL> create or replace package body pkg3 is
      2      procedure p5 is
      3      begin
      4          dbms_output.put_line('PKG3.P5');
      5      end p5;
      6      procedure p6 is
      7      begin
      8          pkg1.p1;
      9      end p6;
     10  end pkg3;
     11  /
    
    Package body created.
    
    SQL> create or replace package body pkg1 is
      2      procedure p1 is
      3      begin
      4          dbms_output.put_line('PKG1.P1');
      5      end p1;
      6      procedure p2 is
      7      begin
      8          pkg2.p4;
      9      end p2;
     10  end pkg1;
     11  /
    
    Package body created.
    
    SQL>
    

The order of the individual objects is irrelevant. Just build the package
specs before the package bodies. Although even that does not really matter...

    
    
    SQL> create or replace package pkg4 is
      2      procedure p7;
      3  end pkg4;
      4  /
    
    Package created.
    
    SQL> create or replace package body pkg4 is
      2      procedure p7 is
      3      begin
      4          dbms_output.put_line('PKG4.P7::'||constants_pkg.whatever);
      5      end p7;
      6  end pkg4;
      7  /
    
    Warning: Package Body created with compilation errors.
    
    SQL> show errors
    Errors for PACKAGE BODY PKG4:
    
    LINE/COL ERROR
    -------- -----------------------------------------------------------------
    4/9      PL/SQL: Statement ignored
    4/43     PLS-00201: identifier 'CONSTANTS_PKG.WHATEVER' must be declared
    SQL>
    

`PKG4` is INVALID because we have not built `CONSTANTS_PKG` yet.

    
    
    SQL> create or replace package constants_pkg is
      2      whatever constant varchar2(20) := 'WHATEVER';
      3  end constants_pkg;
      4  /
    
    Package created.
    
    SQL> select object_name, object_type, status
      2  from user_objects
      3  where status != 'VALID'
      4  order by 1, 2
      5  /
    
    OBJECT_NAME     OBJECT_TYPE     STATUS
    --------------- --------------- -------
    PKG4            PACKAGE BODY    INVALID
    
    SQL> 
    SQL> set serveroutput on size unlimited
    SQL> exec pkg4.p7
    PKG4.P7::WHATEVER
    
    PL/SQL procedure successfully completed.
    
    SQL> select object_name, object_type, status
      2  from user_objects
      3  where status != 'VALID'
      4  order by 1, 2
      5  /
    
    no rows selected
    
    SQL>
    

Anything built using `CREATE OR REPLACE` is always created, it is just marked
as INVALID if there are errors. As soon as we execute it, directly or
indirectly, the database compiles it for us. So, order does not matter. Really
it doesn't.

If the idea of finishing a build with invalid objects concerns you - and I
have some sympathy with that, we are told not to live with broken windows -
you can use the `utlrp` script or in 11g [the UTL_RECOMP
package](http://download-
west.oracle.com/docs/cd/B19306_01/appdev.102/b14258/u_recomp.htm#i999843);
either approach requires a SYSDBA account.

**edit 2**

> The process is based around a build tool which was built by the vendor of
> the product we are integrating with, which is why the only inputs I can give
> to the build process are a list of files in the order they need to be built
> in. If there is a compiler error, the build tool fails, we have to manually
> submit a request for a new build.

This is a political problem not a technical one. Which isn't to say that
political problems can't be resolved with a technical fix, just that the
technical fix is not the best tool for the job. Good luck.

