title: Oracle build order and PL/SQL package dependencies
body: <p>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?).</p>
  
  <p>(Oracle 10.2)</p>
  
  <p><strong>EDIT:</strong></p>
  
  <p>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 <em>in the order they must be compiled in</em>, and acces to those files in source control. If it has that, everything will work quite nicely.</p>
  
  <p><strong>EDIT:</strong></p>
  
  <p>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.</p>
  
  <p><strong>EDIT:</strong></p>
  
  <p>Found this: <a href="http://www.oracle.com/technology/oramag/code/tips2004/091304.html" rel="noreferrer">http://www.oracle.com/technology/oramag/code/tips2004/091304.html</a>
  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.</p>
  
  <p><strong>EDIT:</strong> (with code!)</p>
  
  <p>I know that in general, this sort of thing is not necessary for Oracle, but for anyone who's still interested...</p>
  
  <p>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:</p>
  
  <pre><code>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 &lt;&gt; 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) &lt;&gt; 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;
  </code></pre>
  
  <p>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.</p>
  
  <p><code>:OBJECT_NAME</code> 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.</p>
  
  <p><code>:OBJECT_TYPE</code> I have mostly restricted to <code>PACKAGE BODY</code>, but it shouldn't be too much work to include other types, such as triggers.</p>
  
  <p>One last thing, the object specified by <code>:OBJECT_NAME</code> 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.</p>
  
  <p><strong>UPDATE:</strong> I just discovered <code>user_dependencies</code> and <code>all_dependencies</code>, this code could probably be made much simpler now.</p>