Oracle Build Order and PL / SQL Package Dependencies

Question

I am trying to create a PL / SQL package dependency list so that I can help set up an automatic build script for my packages to run on a test server. Is there a way to start with a single package (ideally a “root” package identified by name) and then find all the dependencies and the order in which they should be compiled? Dependencies are already fully resolved in my personal scheme (so at least I need to start something, but where do I go next?).

(Oracle 10.2)

EDIT:

The build tool used will use the build order and return these files in order from the original control, and then pass them to Oracle for compilation (the build tool itself is written in Python or Java, or in both cases - I do not have access to the source). Basically, the build tool needs to enter a list of files to compile in the order in which they should be compiled, and gain access to these files in the original control. If so, everything will work well.

EDIT:

Thanks for the neat scripts. Unfortunately, the assembly process is mostly out of my hands. The process is based on a build tool that was built by a vendor of a product with which we integrate, so the only resources I can provide to the build process is a list of files in the order in which they should be built. If there is a compiler error, the assembly tool does not work, we must manually send a request for a new assembly. Therefore, listing the files in the order in which they are compiled is important.

EDIT:

Found: http://www.oracle.com/technology/oramag/code/tips2004/091304.html Gives me the dependencies of any object. Now I just need to get the right to order ... If I succeed, I will post it here.

EDIT: (with code!)

I know that in general, this kind of thing is not needed for Oracle, but for those who are still interested ...

I put together a little script that seems to be able to get the build order, so that all packages will be built in the correct order without any dependency errors (regarding pacakges) for the first time:

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 that this is not the prettiest code (global changes are everywhere, etc.), and I will probably send it if I get a chance to clean it today, but now it is producing a build order that seems to run for the first time without problems.

:OBJECT_NAME must be the root object that you want to track all dependencies and assembly order. For me, this is the main package with a single method, which is the entry point to the rest of the system.

:OBJECT_TYPE I was mostly limited to PACKAGE BODY , but there shouldn't be too much work to include other types, such as triggers.

In the latter case, the object indicated by :OBJECT_NAME will not be displayed in the output file, but should be the last element, so you will have to add it to the assembly list manually.

UPDATE: I just discovered user_dependencies and all_dependencies , this code will probably be a lot easier now.

Answer 1

Actual solution: the above script gives the correct build order. Perhaps you can rewrite "better", but I will leave this as an exercise for the reader .;)

After some discussion, the build tool will perform n (4, in fact) assemblies in the line before error messages. It would also help resolve dependency compilation errors if the build order is incorrect, but I would rather just get the build order for the first time.

Answer 2

If you are really dealing with only PL / SQL packages, you do not need to sweat the build order. First create all the package specifications. Then you can expand all the package bodies and they will compile because their dependencies are package specifications.

If you have some package specifications that depend on other specifications — if you have packages that declare, say, constants, subtypes, or link pointers that are used in the signatures of packed procedures — then you need to collect these specification packages in the first turn. But there should be enough of them so that you can organize them in the script assembly manually.

change

It looks like they will do incremental AND "clean sweep" builds, so the assembly order will be of greatest importance when they clean the environment and restore it.

This does not change anything.

Here is an extended example. I have a scheme 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> 

Interestingly, the procedure in PKG1 calls the procedure from PKG2, the procedure in PKG2 calls the procedure from PKG3, and the procedure in PKG3 calls the procedure from PKG1.

Q. How does this cyclic dependency work?
A. This is not circular dependence ....

 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 dependent objects are package bodies, all reference objects are packed specifications. Therefore, if I do not create a circuit, it really does not matter which 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 does not matter. Just create package specifications in front of the package body. Although even this 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> 

Everything that is built using CREATE OR REPLACE is always created, it is simply marked as INVALID if there are errors. As soon as we execute it, directly or indirectly, the database compiles it for us. So the order doesn't matter. This is actually not the case.

If the idea of ​​completing the assembly with invalid objects concerns you - and I have some sympathy for this, we are told not to live with broken windows - you can use the utlrp script or the UTL_RECOMP package in 11g; any approach requires a SYSDBA account.

change 2

The process is based on the assembly tool created by the seller of the product with which we integrate, therefore the only inputs I can give the assembly process is a list of files in the order in which they should be built-in. If there is a compiler error, the build tool does not work, we have to manually send a request for a new build.

This is a political issue, not a technical one. This does not mean that political problems cannot be solved with a technical solution, namely, that a technical correction is not the best tool for work. Good luck.

Answer 3

Take a look at the 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; / 

Answer 4

One little thing to watch out for when walking the dependency tree. Dependencies for uncompiled programs are not displayed ...

 SQL> drop package constants_pkg 2 / Package dropped. SQL> create or replace package body pkg4 is 2 procedure p7 is 3 begin 4 dbms_output.put_line('PKG4.P7::'||zzz_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 'ZZZ_CONSTANTS_PKG.WHATEVER' must be declared SQL> 

So the body for PKG4 is INVALID because ZZZ_CONSTANTS_PKG does not exist.

 SQL> create or replace package zzz_constants_pkg is 2 whatever constant varchar2(20) := 'WHATEVER'; 3 end zzz_constants_pkg; 4 / Package created. SQL> 

But the body for PKG4 is still INVALID, so the following query does not return its dependency on ZZZ_CONSTANTS_PKG ....

 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 PKG4 PACKAGE BODY PKG4 PACKAGE 7 rows selected. SQL> 

Now let's compile PKG4 and re-query the dependencies ....

 SQL> alter package pkg4 compile body; Package body altered. 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 PKG4 PACKAGE BODY PKG4 PACKAGE PKG4 PACKAGE BODY ZZZ_CONSTANTS_PKG PACKAGE 8 rows selected. SQL> 

Answer 5

You don't need the build order - just create packages using "CREATE OR REPLACE ..." in each file, and then compile them in a two-level nested loop - each pass in the inner loop compiles everything that is still invalid, and the outer loop is used to checking the number of remaining invalid objects and setting a threshold to maximize the performance of the inner loop. In practice, I never saw that the number of passes was above three.

If you have several schemes involved in dependencies, look at the running Oracles utlrp.sql script, which works according to the schemes and sets up some infrastructure to control the process - this requires a privileged account.

Also, if you extend the original control to include views, make sure the scripts use "CREATE OR REPLACE FORCE VIEW ..." to create views that have unsatisfied dependencies when they are created.

An example script that I use:

 set serveroutput on declare cursor invalidObjCur is select object_name, object_type from user_objects where status <> 'VALID' ; compileStmt varchar2(4000); passCount pls_integer := 0; maxPasses pls_integer := 5; lastInvalidCount pls_integer := 32000; objectCount pls_integer; continue boolean := TRUE; begin dbms_output.enable(1000000); while (continue) loop passCount := passCount + 1; dbms_output.put_line('Pass '||passCount); objectCount := 0; for curRow in InvalidObjCur loop if curRow.object_type = 'PACKAGE BODY' then compileStmt := 'alter PACKAGE '||curRow.object_name||' compile body'; else compileStmt := 'alter '||curRow.object_type||' '|| chr(34)||curRow.object_name||chr(34)||' compile'; end if; begin execute immediate compileStmt; exception when others then null; end; objectCount := objectCount + 1; end loop; dbms_output.put_line('Recompilations attempted: '||objectCount); continue := (passCount < maxPasses) and (objectCount < lastInvalidCount); lastInvalidCount := objectCount; end loop; dbms_output.put_line('***** Remaining Invalid ********'); for curRow in InvalidObjCur loop dbms_output.put_line(curRow.object_type||' '||curRow.object_name); end loop; dbms_output.put_line('********************************'); end; / 

Answer 6

Add the following command to the top of the script:

SET VERIFY OFF

this will allow your scripts to work without checking and therefore can be executed in any order.

You can later query DBA_ERRORS to get all errors and warnings in your packages, views, and types.

Answer 7

Try this instead of 11.1 and higher. Run the script in any order. At the end you will receive the following command: (Change the command parameters according to your needs)

 -- Compile invalid objects EXEC DBMS_UTILITY.compile_schema(USER, FALSE); 

Learn more about DBMS_UTILITY.compile_scema