Menu
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.
329
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; 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.
111
Look at the following script from http://www.oracle-base.com/articles/misc/RecompilingInvalidSchemaObjects.php
SET SERVEROUTPUT ON SIZE 1000000
BEGIN
FOR cur_rec IN (SELECT owner,
object_name,
object_type,
DECODE(object_type, 'PACKAGE', 1,
'PACKAGE BODY', 2, 2) AS recompile_order
FROM dba_objects
WHERE object_type IN ('PACKAGE', 'PACKAGE BODY')
AND status != 'VALID'
ORDER BY 4)
LOOP
BEGIN
IF cur_rec.object_type = 'PACKAGE' THEN
EXECUTE IMMEDIATE 'ALTER ' || cur_rec.object_type ||
' "' || cur_rec.owner || '"."' || cur_rec.object_name || '" COMPILE';
ElSE
EXECUTE IMMEDIATE 'ALTER PACKAGE "' || cur_rec.owner ||
'"."' || cur_rec.object_name || '" COMPILE BODY';
END IF;
EXCEPTION
WHEN OTHERS THEN
DBMS_OUTPUT.put_line(cur_rec.object_type || ' : ' || cur_rec.owner ||
' : ' || cur_rec.object_name);
END;
END LOOP;
END;
/
If you love us? You can donate to us via Paypal or buy me a coffee so we can maintain and grow! Thank you!
Donate Us With
