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.