Skip to Main Content
  • Questions
  • How to get dependencies among pl/sql objects

Breadcrumb

Question and Answer

Tom Kyte

Thanks for the question, Pratap.

Asked: November 26, 2001 - 11:49 pm UTC

Last updated: September 18, 2009 - 12:41 pm UTC

Version: 8.1.6

Viewed 10K+ times! This question is

You Asked

Hi Tom

We have a large number of pl/sql packages and procedures (.sql files). Now I want to create a file that will create all the packages and procedures in the database like this -

create_objects.sql, this will have following contents -

@@pack1.sql
@@pack2.sql

Can I generate create_objects.sql using sql/plsql ? In this case how do we find the sequence in which the packages are to be created taking in view the dependencies among them.

Thanks and regards

Pratap


and we said...

well, you cannot (there can be recursive relationships) -- and the really cool thing is -- you DON'T need to!!!

Just create all of the specifications.
Then create all of the bodies.

There you go. Everything will compile and everything will be valid. Never never use standalone procedures/functions -- always use packages -- and installation is a snap. specs, then bodies. Even if the specs depend on eachother -- it won't matter WHAT order you create them in as the compilation of the bodies will validate the specs. In the end -- everything is AOK.



Rating

  (33 ratings)

Is this answer out of date? If it is, please let us know via a Comment

Comments

Thanks!!

Pratap, November 27, 2001 - 12:14 pm UTC


Great question...

Tony, November 27, 2001 - 4:02 pm UTC

But how would you automatically create the files? I need to do this frequently when moving new/modified packages up the chain to production. My current method is a bit embarrassing to reveal...

Tom Kyte
November 27, 2001 - 8:23 pm UTC

Well, something like this:

set feedback off
set heading off
set termout off
set feedback off
set heading off
set termout off
set linesize 1000
set trimspool on
set verify off
spool &1..sql
prompt set define off
prompt select sysdate from dual

select decode(line,1,'/' || chr(10) || 'create or replace ', '' ) ||
text text
from user_source
where type = 'PACKAGE'
order by name, line;
prompt /
prompt set define on
spool off
set feedback on
set heading on
set termout on
set linesize 100

will get all of the specs, change PACKAGE to PACKAGE BODY and you would get all of the bodies...

Why never never

Jim, November 28, 2001 - 6:20 am UTC

Tom
If you get a minute, please list your reasoning
behind never never using procedure etc always use packages.
I know of a couple of reasons (compilation like you said above, executing one procedure in a package causes the whole package to be loaded into the shared pool so if you have packaged procedures commonly used together there maybe some benefit) but would be interested in your thoughts


Tom Kyte
November 28, 2001 - 7:45 am UTC

1) packages break the dependency chain
2) packages break the dependency chain
and
3) packages break the dependency chain

did I mention -- they also break the dependency chain.
see
</code> http://asktom.oracle.com/pls/asktom/f?p=100:11:::::P11_QUESTION_ID:286816015990 <code>


Also, entire packages are NOT LOADED -- they are demand paged in as needed, just like an operating system. This is one reason people claim to not want to use packages -- they assume they are fully loaded -- but they are not.

Packages increase your namespace -- you get Pkg.Proc. Instead of 5,000 little procedures with meaningless names, you can have 500 packages with good module names, each with 10 procedures that have meaningful names (that might be the same as the name of a procedure in another package). Not only that -- but the procedures are logically grouped together.

Packages allow for overloading.

Packages allow for procedure hiding -- I might have 10 procedures in the spec, 20 in the body. You never need know about the other 10.

Packages promote good coding. When I see people using standalone procedures what I many times see are 5,000 or more LINES OF CODE in a single procedure. That totally freaks me out (big time disbelief). Then they get upset when it gets too large to compile. I have no sympathy there -- I've never come anywhere close to hitting any limits, but then again -- I try to make sure my subroutines fit on a screen so I can see the top of the procedure and the bottom of the procedure without having to hit page up/down.
Packages allow you to maintain a state.

Packages allow you to create types.

and so on....

I use standalone procedures/functions only to demonstrate a concept, never for real code.

Getting the dependencies.

Kamaljeet Singh, November 28, 2001 - 10:47 am UTC

Tom the queries might be bad , but this does works. And why i might need this is because whne we create a copy of an existing schema. The log generates a lot's of error if code is not compiled in dependency order. Hence debugging is a problem.

I faced the same problem. One way out is

first Compile the package
then Procedures and functions
then package bodies.

Now for dependency part..
/* Get the dependencies */
insert into temp_dep select name,type,referenced_name,referenced_type from all_dependencies where owner = &schemaName and
type in ('PROCEDURE','FUNCTION') and referenced_type in ('PROCEDURE','FUNCTION');

and then
following query will give the code in order of dependencies.

Select nvl(a.lvl,0),b.name,b.line,
decode(b.line,1,'CREATE OR REPLACE '||b.text,b.text) stmt
from ( select max(level) lvl,name from temp_dep where decode(type,'FUNCTION','PROCEDURE',type) = 'PROCEDURE'
start with name like '%' and decode(type,'FUNCTION','PROCEDURE',type) = 'PROCEDURE'
connect by prior name = referenced_name
group by name ) a , dba_source b
where b.owner = &schemaName
and decode(b.type,'FUNCTION','PROCEDURE',type) = 'PROCEDURE'
and b.name = a.name(+)
order by 1,2,3;

Since we cannot create a procedure wihich can refer to non exixtent procedure or function. There should be no loop dependencies in the query.
Also since we compile the packages first. Procedures and functions compile oK.

It works for me nicely.


Tom Kyte
November 28, 2001 - 12:56 pm UTC

I don't use procedures, I don't use functions. (see other comment above for why this is)

If you just use packages -- you need no such complicated processing at all.

All it takes is one recursive dependency and your method is broken.

You can certainly create a procedure which refers to a non-existent procedures. It compiles with errors (its invalid). We don't care what order you create stuff in (just look at an IMP!)

There are in most all 8i databases -- built in dependency loops if you have java installed (unfortunately, it can cause issues with queries such as yours)

Besides, I don't really ever care what order things are created in -- they'll all create. Some will be invalid, some won't. As people run them - they will fix themselves.

oh well, if you have a query that works on your system, go for it -- it takes along time to run and if you just use packages -- is totally not necessary (i mean running that query is just step one. Now you have to extract the code -- IN that order! -- something like "@getspec", "@getbody" just looks sooo nice in comparision)




Thanks for the followup!

Tony, November 28, 2001 - 2:01 pm UTC

Tom - the code in your reply worked great. The decode statement at the beginning is very clever - just the *magic* I needed.

Never Never continued

Jim, November 29, 2001 - 1:14 am UTC

:) Now I get it packages break the dependancy chain !
Very interesting about the entire package NOT getting
loaded, I was told on an Oracle Education course by
the instructor that it was !
Thanks


Loading Of Package In Memory

Anup, November 29, 2001 - 10:31 am UTC

Hi Tom

I Was Also Aware That Entire Packages Gets Loaded Into Memory When The Procedure Within The Package Is Called For The First Time.

I Checked The Following Link And It Also Mention The Same

</code> http://download-east.oracle.com/docs/cd/A81042_01/DOC/server.816/a76965/c17pckgs.htm#5556 <code>Under The Heading Performance Improvement.

Thanks For Your Great And Simple Explanations.



compilation in Oracle Applications

A reader, April 15, 2003 - 4:31 pm UTC

Hi

I wonder why we normally have to run utlrp.sql several times to compile invalid objects in Oracle Applications? Once I had 100 invalid objects, after first run I ended up with over 500 invalid packages, I had to run the script 4 or 5 times to compile everything, I understand it may be because of dependencies. How can we compile PL/SQL objects successfully in the first go?

Tom Kyte
April 16, 2003 - 9:23 am UTC

just let them compile themselves?

that is what i do. I've never lost sleep over it knowing they will do it all by themselves.

If you have my book "Expert one on one Oracle" -- i describe a technique whereby you can compile all things in a schema successfully. Basically the psuedo code is:

create table already_tried( object_type, object_name );


create function get_invalid_object return varchar2
as
begin
select ... into ... from user_objects
where not exists ( select null from already_tried a
where a.object_type = user_objects.object_type ...)
and status = 'INVALID'
and rownum = 1;

return ...
end;


begin
delete from already_tried;
loop
compile the output of get_invalid_object;
insert into already_tried;
end loop;
exception
when no_data_found then done
end;


it works by getting AN invalid object that we haven't yet tried to compile. It compiles it (which will recursively compile all needed objects -- the first invalid object may well fix ALL invalid objects).

We remember we did that one

Go onto the next.

done. Now at the end, you might have invalid objects -- but they will remain invalid forever as they quite simple cannot be compiled.



Invalid Function

atul, June 25, 2003 - 1:32 pm UTC

Hi tom,

i have one query..
We use only packages not standalone functions/procedures..

When i see status of all objects in my database
it shows one function as invalid ..

How should i debug this??
as i'm not getting any clues??


Thanks.
atul

Tom Kyte
June 25, 2003 - 8:10 pm UTC

confusion

a) we only use packages
b) we have an invalid function

I cannot fathom b from a so you will have to be "more clear"

Who uses what?...

J, December 18, 2003 - 2:32 pm UTC

Hello!

Here's my situation: I've inherited an undocumented system - 40+ packages, each with approx 15-20 procedures, and (suprise!) 30+ stand-alone procedures and functions. Trying to get a flow of the system is quite a challenge due to inter/intra-package calls, etc.

Is there anything in the DD that I can query to help build a flow of how this multitude of code ties together? If not, can you suggest an approach (other than hari-kari) that I could undertake to get a handle on this beastie?

Many Thanks!
- J

Tom Kyte
December 18, 2003 - 3:50 pm UTC

DBA_DEPENDENCIES shows who calls who at the package/procedure level. You can use that as a starting point.

recommend -- copy dba_dependencies into a temporary work table (create table as select) and index/query the copy. doing tons of queries on the view will be "painful"

Who uses what... (PS...)

J, December 18, 2003 - 2:40 pm UTC

Oops.

Forgot to mention that all of the stored procedures are within the same schema - there's no remote invocations, etc.

Thanks again!
- J

drop objects

Thiru, May 26, 2004 - 11:08 am UTC

There are tons of object TYPE,TYPE BODY in our system. When we reset the schema
for development, we need to drop everything ( without dropping the user)
and recreate them.
How do we go about dropping the TYPE in order of heirarchy (dependencies).
I tried to see if we can get the dependency from dba_dependencies but this does
not give the hierarchy in which to drop the objects.
select name,type,referenced_owner,referenced_name,referenced_type,dependency_type
from dba_dependencies where owner=< > and name=<object_name>;


Tom Kyte
May 26, 2004 - 1:13 pm UTC

ops$tkyte@ORA9IR2> create type t1 as object ( x int );
  2  /
Type created.
 
ops$tkyte@ORA9IR2> create type t2 as table of t1;
  2  /
Type created.
 
ops$tkyte@ORA9IR2> drop type t1;
drop type t1
*
ERROR at line 1:
ORA-02303: cannot drop or replace a type with type or table dependents
 
 
ops$tkyte@ORA9IR2> drop type t1 <b>force;
Type dropped.</b>

 
ops$tkyte@ORA9IR2> drop type t2;
 
Type dropped.
 

Is this the correct query for getting objects in order for dropping

A reader, May 26, 2004 - 12:09 pm UTC

select OBJECT_TYPE,OBJECT_NAME
from DBA_OBJECTS where object_id in (
select object_id
from public_dependency
connect by prior object_id = referenced_object_id
start with referenced_object_id IN (
select object_id from DBA_OBJECTS
where owner = <username>
))
/


Tom Kyte
May 26, 2004 - 3:56 pm UTC

i don't know, i would never even try bothering as there is "cascade constraints" for tables and "force" for types.

what are you trying to drop that isn't dropping.

(actually, i would just as soon drop the user and recreate, lazy guy that I am. let the database do the work)

A reader, May 27, 2004 - 6:27 pm UTC

The application uses dbms_lock package and dropping a user times out waiting to get the dbms_lock. All the sessions have to be killed or closed in order to drop. Is there no way we can get one query to drop all objects in order of their dependencies ( yes if required separate for tables and types. In fact all our tables are from types).

Tom Kyte
May 27, 2004 - 8:58 pm UTC

use force, no need for "any order"?

you have an application that does the equivalent of a drop user on demand? frequently? why?

re: Is this the correct query for getting objects in order for dropping

Tak Tang, May 28, 2004 - 9:05 am UTC

I'll try to use reason and logic on this . . .

I notice you are using a SELECT .. FROM .. WHERE .. IN.
'IN' is a 'SET' construct, so there is no implicit ordering. I have seen oracle do a SORT on an IN before, although I do not think it will in this case. Secondly, it is only required to pull back rows from DBA_OBJECTS which correspond to those in the IN list. It is not obliged to pull them back in the same order, since no order is implied.

So, no, it is definitely not correct.

'start with referenced . . . IN '
I think you have your logic backwards. The START WITH clause specifies rows that appear first (well, top most level). Yet you select objects which are referenced. ie, the objects you are trying to drop first are those which are referenced, and since they are referenced, you'll get a 'cant drop this object because it is referenced'. I think you need to start with objects that are NOT REFERENCED.

In the past, I've always generated a script from user_objects thats drops objects like this :-

select 'drop ' || object_owner || '.' || object_type || ' ' || object_name || ';' from user_objects;

and just run it over and over until there were no more objects. I see from Tom's post that I should be using 'FORCE' and 'CASCADE' so I would only need to run it once.



I have a theory (ie a theorem that has not been proved) that you can not pull back the list of objects in the correct order for dropping, in only one or two queries using oracle 9 or before, because of the way that hierarchical queries work.

To review how oracle processes hierarchical queries, I'll quote from the SQL Reference manual

</code> http://download-west.oracle.com/docs/cd/B10501_01/server.920/a96540/queries4a.htm#2053937 <code>

<QUOTE TITLE='SQL Reference' FILE_NAME='A96540.pdf' PAGE='8-4'>

Oracle uses the information from the hierarchical query clause to form the hierarchy using the following steps:

1. Oracle processes the WHERE clause either before or after the CONNECT BY clause depending on whether the WHERE clause contains any join predicates (as described in the preceding bullet list).

2. Oracle selects the root row(s) of the hierarchy -- those rows that satisfy the START WITH condition.

3. Oracle selects the child rows of each root row. Each child row must satisfy the condition of the CONNECT BY condition with respect to one of the root rows.

4. Oracle selects successive generations of child rows. Oracle first selects the children of the rows returned in step 3, and then the children of those children, and so on. Oracle always selects children by evaluating the CONNECT BY condition with respect to a current parent row.

</QUOTE>


A side effect of 3) and 4) is that oracle always pulls back the SHORTEST path.


Consider the following schema :-



create or replace
procedure a is
begin
null; -- a depends on nothing
end;
/

create or replace
procedure b is
begin
a; -- b depends on a
end;
/

create or replace
procedure c is
begin
b; -- c depends on b
end;
/

create or replace
procedure d is
begin
c; -- d depends on c
end;
/


create or replace
procedure e is
begin
a; -- e depends on a
d; -- and d
end;
/


This is my artists impression :-

a
|\
| b
| |
| c
| |
| d
|/
e

We can tell visually that the correct drop order would be 'e', 'd', 'c', 'b', 'a' - this is the longest path.

The only object that can be dropped is 'e', because no other objects depend on it (ie it is not referenced).

Refferring to step 3, we then select back child rows. ie rows which 'e' depend on. We can see clearly that it will pull back 'a' and 'd'. But 'b' references 'a', so we can not drop it yet.

To do this properly, we would need some mechanism to exclude 'a', until all the objects that depend on it have been processed, but currently we can not use a sub-query in the connect-by clause.

So I'm afraid that (in my fallible opinion) it can not be done with one or two queries.




You will need to resort to PL/SQL to generate the a list of objects in correct drop order - essentially implement your own hierarchical query processor. It is a lot of effort for something that is unnecessary because of FORCE and CASCADE.

Alternatively, you could have a loop that fetches objects that are not referenced; drops them; and repeats until there are no more objects.

Beware - you need to exclude objects owned by system and sys. Drop those and your DBA will come looking for you. I have to confess that I have actually done that before by mistake. Fortunately it was on a test database. Unfortunately I was the DBA, so there was no where to hide :-)


Takmeister


Tom Kyte
May 28, 2004 - 11:33 am UTC



I take it you were refering to one of the other posts -- I've not myself supplied any queries.


I take the "force" route. no order needed.

how to find all private functions/procedures in a package without parsing

Anand Kothapeta, November 10, 2004 - 3:26 pm UTC

Tom,
I have a requirement to draw a dependency tree, for example if a procedure a in package p1 calls procedure b in package p2 then I can do this.
p1.a
|
+----p2.b

what I couldn't do was if a is a private procedure / function or even with in a package procedure a calls b and if either one of them are private. Is there a way to find all private functions/procedures in a package with out parsing.


Tom Kyte
November 10, 2004 - 8:18 pm UTC

not sure how you can do the first part (public procedures) without parsing? how do you think you can do this without actually building a parse tree of some sort?

how to find all private functions/procedures in a package without parsing

Anand Kothapeta, November 11, 2004 - 4:02 pm UTC

Tom You were right that I have to still do some parsing, But good thing with the public function/procedures I already know the procedure/function names it makes it little easier (eg: user_procedure will tell me all the names of procedures in a package, and atleast package dependency info from all_dependencies, so I know what to look and where to look). I only have problem with private functions as parsing gets very difficult depending on coding style, nested procedures etc..

thanks,

Tom Kyte
November 11, 2004 - 4:27 pm UTC

and overloading (even for "public" functions and so on...)

i don't see any way to do this correctly without parsing (public or private functions...)

how to find all private functions/procedures in a package without parsing

Anand kothapeta, November 11, 2004 - 4:08 pm UTC

i meant user_arguments not user_procedures in the previous note

dependency

P.Karthick, May 10, 2005 - 5:53 am UTC

Hello Tom,

I have a procedure. I was written a long time back and it is called in lot of other procedures and triggers. Now i have to do a patch in the procedure that to in the IN PARAMETER ( iam adding one more in parameter). So because of this i have to patch all the procedures and triggers that is calling it. I have manually identified all the procedures and triggers from which this procedure is called but still iam not comfortable. So i think oracle can help me in this because we strongly relay on it.

I would like to have a query that gives me all the triggers, functions and procedures that calls this procedure.


Thank you tom

P.karthick.

sorry tom

P.Karthick, May 10, 2005 - 7:12 am UTC

i have found the answer in this site itself.

</code> http://asktom.oracle.com/pls/asktom/f?p=100:11:::::P11_QUESTION_ID:3786863562434 <code>

thank you.

P.karthick

Built-in function to display the current package/proc?

ht, May 14, 2005 - 1:11 am UTC

Hi Tom,
I scanned the dba_source table for a built-in function that would display a package/proc name. Does one exist?

For example, when calling a proc, I would like to pass in the proc that called it. Instead of hardcoding the proc's name, I would like to do something like "select current_proc_name from dual;".

Thank you.

ht

Tom Kyte
May 14, 2005 - 9:34 am UTC

Link Not working

Shivdeep Modi, December 05, 2006 - 11:53 am UTC

Tom Kyte
December 05, 2006 - 10:12 pm UTC

How to get dependencies among pl/sql objects

Venkat, May 12, 2008 - 11:35 am UTC

Tom:

This is question about finding all the objects used in the procedure.
We need to find all the DML operation source and target tables.

Here is some of examples:

Example1:
insert into emp
select e.* from emp_old e, dept_old d
where d.dept_no=e.deptno;

The result should look like below,

Target: emp
Source: emp_old, dept_old

Example2:
update emp e
set e.sal=(select p.sal+.1*p.sal from emp_pro p where p.empno=e.empno)

Target: emp
Source: emp_pro

Example3:
insert into emp
select e.* from emp_old1 e, dept_old1 d
where d.dept_no=e.deptno
and e.condition=1
union all
select e.* from emp_old2 e, dept_old2 d
where d.dept_no=e.deptno
and e.condition=2
union
select e.* from emp_old3 e, dept_old3 d
where d.dept_no=e.deptno
and e.condition=3

Target: emp
Source: emp_old1, dept_old1, emp_old1, dept_old3, emp_old3, dept_old3

Could you please throw some light how to get this? Thanks.

Venkat
Tom Kyte
May 12, 2008 - 1:58 pm UTC

we do not track it to that level.

We do not have that information for you in any dictionary table.

Find level of procedure inside a package. body

Vijai Narayanan SV, March 06, 2009 - 12:16 am UTC

Hi Tom
I have a small query,
please Help me out.
If this is not the right forum, please redirect me.
i have a package body with sub procedures not defined in spec.
pk A
begin
sp1
begin
sp2
begin
/*code*/
end;
end;
end

i need the hirarchy of the procedures like below,
proc_name level
sp1 1
sp2 2

kindy help me outt in solving this problem.
Tom Kyte
March 06, 2009 - 10:12 am UTC

that information is not captured or exposed anywhere, you would have to write a code parser to get that yourself if you really really needed it.

Dependency tree of packages

Lise, March 25, 2009 - 7:27 pm UTC

Hi,
We have lots of packages with lots of dependencies. Recompilation etc works fine as you highlighted before. However, I need to display the relationship between packages and hence need to produce a dependency tree.
Say I have these packages:

CREATE OR REPLACE PACKAGE pck_level1 IS
PROCEDURE p_main;
END pck_level1;
/
CREATE OR REPLACE PACKAGE pck_level2 IS
PROCEDURE p_main;
END pck_level2;
/
CREATE OR REPLACE PACKAGE pck_level3 IS
PROCEDURE p_main;
END pck_level3;
/
CREATE OR REPLACE PACKAGE pck_level4 IS
PROCEDURE p_main;
END pck_level4;
/
CREATE OR REPLACE PACKAGE pck_level5 IS
PROCEDURE p_main;
END pck_level5;
/
CREATE OR REPLACE PACKAGE pck_level6 IS
PROCEDURE p_main;
END pck_level6;
/
CREATE OR REPLACE PACKAGE pck_level7 IS
PROCEDURE p_main;
END pck_level7;
/
CREATE OR REPLACE PACKAGE BODY pck_level1 IS
PROCEDURE p_main IS
BEGIN
pck_level2.p_main;
END p_main;
END pck_level1;
/
CREATE OR REPLACE PACKAGE BODY pck_level2 IS
PROCEDURE p_main IS
BEGIN
pck_level3.p_main;
END p_main;
END pck_level2;
/
CREATE OR REPLACE PACKAGE BODY pck_level3 IS
PROCEDURE p_main IS
BEGIN
pck_level4.p_main;
END p_main;
END pck_level3;
/
CREATE OR REPLACE PACKAGE BODY pck_level4 IS
PROCEDURE p_main IS
BEGIN
pck_level5.p_main;
END p_main;
END pck_level4;
/
CREATE OR REPLACE PACKAGE BODY pck_level5 IS
PROCEDURE p_main IS
BEGIN
pck_level6.p_main;
END p_main;
END pck_level5;
/
CREATE OR REPLACE PACKAGE BODY pck_level6 IS
PROCEDURE p_main IS
BEGIN
pck_level7.p_main;
END p_main;
END pck_level6;
/
CREATE OR REPLACE PACKAGE BODY pck_level7 IS
PROCEDURE p_main IS
BEGIN
pck_level1.p_main;
END p_main;
END pck_level7;
/

I would like to see something like this if I choose to see dependencies for PCK_LEVEL2 say:
NAME TYPE REFERENCED_NAME REFERENCED_TYPE
PCK_LEVEL2 PACKAGE BODY PCK_LEVEL3 PACKAGE
PCK_LEVEL3 PACKAGE BODY PCK_LEVEL4 PACKAGE
PCK_LEVEL4 PACKAGE BODY PCK_LEVEL5 PACKAGE
PCK_LEVEL5 PACKAGE BODY PCK_LEVEL6 PACKAGE
PCK_LEVEL6 PACKAGE BODY PCK_LEVEL7 PACKAGE
PCK_LEVEL7 PACKAGE BODY PCK_LEVEL1 PACKAGE
PCK_LEVEL1 PACKAGE BODY PCK_LEVEL2 PACKAGE

If run something like this:
WITH temp AS
(SELECT NAME
,TYPE
,referenced_name
,referenced_type
FROM dba_dependencies
WHERE owner = :USERSCHEMA
AND TYPE IN ('PACKAGE', 'PACKAGE BODY')
AND referenced_type IN ('PACKAGE', 'PACKAGE BODY'))
SELECT SYS_CONNECT_BY_PATH (NAME,'.') scbp
,LEVEL lvl
,t.*
FROM temp t
WHERE 1 = 1
START WITH NAME = 'PCK_LEVEL2'
CONNECT BY NAME = PRIOR referenced_name AND referenced_name <> NAME

I get the error ORA-01436 CONNECT BY loop in user data.
I am using Release 9iR2.


Tom Kyte
March 30, 2009 - 2:43 pm UTC

well, forget the query for a moment.

package's BREAK the dependency change. you do not have a complicate hierarchy. It only goes ONE DEEP.

If a package body is dependent on some other package - the buck stops THERE.

If you start at pck_level2, that returns a package body. That package body is dependent on the package (just the package) pck_level2 and pck_level3, that is all - done.

compile your specs, then compile the bodies in any order you want.


or, if you are like me, just compile them IN ANY ORDER YOU WANT knowing they will auto-recompile if necessary.


WITH temp AS
    (SELECT NAME
          ,TYPE
          ,referenced_name
          ,referenced_type
    FROM   dba_dependencies
    WHERE  owner = user
    AND    TYPE IN ('PACKAGE', 'PACKAGE BODY')
    AND    referenced_type IN ('PACKAGE', 'PACKAGE BODY'))
select lpad('*',2*level,'*') || referenced_type || '.' || referenced_name nm
  from temp
 start with name = 'PCK_LEVEL2'
connect by prior referenced_name = name and prior referenced_type = type
/

package program units calling each other

Stewart Bryson, June 16, 2009 - 3:36 pm UTC

Tom:

You describe how packages break the dependency chain... such that I can even have two packages (pkg_a and pkg_b, for instance) that use each other's program units, and they will compile as long as the specs are created first.

In general: would you consider having dependencies among pkg_a and pkg_b a good coding practice, or not? I've always avoided it... but now, I'm not certain why. How about with types and packages... would the answer be the same?

From this posting (and the others referenced here), it seems like your opinion is that I shouldn't get caught up on having inter-depencies (for lack of a better term).

By the way... I'm speaking next door to you at ODTUG. If your room gets full... please send a few my way. :-)
Tom Kyte
June 17, 2009 - 11:12 am UTC

having dependencies between package specifications is pretty much unavoidable in an application of any size.

between *specs*, not bodies.

Dependencies going both ways

Stewart Bryson, June 17, 2009 - 2:50 pm UTC

I just want to make sure I was clear... so apologies if you have to restate yourself.

There is no downside, in your opinion, from inter-dependencies, such as the following:

CREATE OR REPLACE PACKAGE p1
IS
PROCEDURE prog1;
END p1;
/

CREATE OR REPLACE PACKAGE p2
IS
PROCEDURE prog2;
END p2;
/

CREATE OR REPLACE PACKAGE BODY p1
IS
procedure prog1
IS
BEGIN
-- do a bunch of stuff
p2.prog2;
-- do some other stuff
END prog2;
END p1;
/

CREATE OR REPLACE PACKAGE BODY p2
IS
procedure prog2
IS
BEGIN
-- do a bunch of stuff
p1.prog1;
-- do some other stuff
END prog2;
END p2;
/

Tom Kyte
June 17, 2009 - 3:31 pm UTC

I can see downsides to that - i wouldn't want mutually dependent dependencies necessarily.

I see it more like a "tree"

utility_pkg

called by pkg1, pkg2, pkg3

and maybe pkg1 calls pkg2


and there might be a valid use case where pkg1 calls pkg3 and pkg3 calls pkg1 - but it would be "the unusual case"

Thanks

Stewart Bryson, June 17, 2009 - 3:53 pm UTC

That's what I've always avoided... but this posting by you made me aware that it's actually possible. However, it doesn't sit well... and I usually take the utility package approach, as you've demonstrated.

finding dependencies of object in package body

A reader, June 23, 2009 - 1:43 pm UTC

hi tom,

i have a package with about 15 procedures.
each procedures are quite lengthy.

recently, i need to find the dependency between the package and a particular table.

so i issue a

select * from user_dependencies where referenced_name = 'TAB1';

however i am only able to see the dependent package body name, but not the individual procedure inside the package that reference this table tab1.

Is there any work around for it?

Please advise,
Regards
Noob
Tom Kyte
June 26, 2009 - 9:17 am UTC

... each procedures are quite lengthy. ...

that is a problem, you ought to fix that so the code is not embarrassing to you when someone else inherits it.


The dependency is between the package and the table, that is all we care about and all we record.

You can of course query user_source where name = 'THAT PKG" and upper(text) like '%TAB1%' to find probable hits on it

finding dependencies of object in package body

Nani, September 17, 2009 - 10:56 am UTC

Hi Tom,

We need to find the private/public procedures inside the package uses the specified table name.

Please advice

Thanks in advance.


Tom Kyte
September 17, 2009 - 1:37 pm UTC

you cannot, we do not track that, you have the source code - if you want to parse it you can - but we do not track down to the individual procedure/function name

we can give you the line number and the package name.

Object dependency

A Reader, September 18, 2009 - 10:32 am UTC

Can you please provide sample code to find the procedures inside the packages which are using a required table.


Tom Kyte
September 18, 2009 - 12:41 pm UTC

I shall repeat myself:

... you cannot, we do not track that, you have the source code - if you want to parse it you can ...


I have no such code, I do not plan on writing such code. Such code will return things like:

this is used by p
this is used by p
this is used by p

but the problem is there are 4 versions of P in my package, now what.

Line number and package name has always more than satisfied me, I could care less what routine in the package used it - that the package does is sufficient.

how to find "dependencies of objects in a database"

Aryan Naim (BSIT &amp; SCJP), September 30, 2009 - 11:20 am UTC

First this is computer science question & graph theory and can be implemented easily if really needed. Assumptions:
1) since this is a complex task than its being executed from a information system and not directly through Oracle
therefore possibly JAVA,C#,C++,etc.

2) the info system will be object oriented & Oracle tables will be object tables in order to save hours of useless parsing through oracle table dictionary or query outputs.

The Solution:
1) A database is a cyclic graph
explanation: Tables can refer to themselves such as a (subtype)Manager is an (super type)Employee and therefore employees can Supervise(relationship) other employees

Therefore you would need to create a N-ary Tree data structure with the following methods at least

insert
delete
search
2) you will populat your tree with objects that will later correspond to object tables in oracle

2) perform Depth-first-search as an exploratory algorithm to find all dependencies, while performing the DFS you will do one extra step that is really important which is aside from the the DFS implementation when ever you find a new Node(object/table/entity) in your data structure you will add it to a SET data structure in order to avoid adding the same Node or table twice.

Then for example, one application could be to delete all objects in the database at once.

4)Then you will delete the tables by issuing SQL delete commands through loop from the first element until the last in the SET data structure, this will make sure to delete the tables and dependencies in order from children all the way up to the root node/table/object.




Yuri, October 04, 2009 - 9:42 am UTC

I have written the ODA tool shows dependence of database objects in PL/SQL code as a tree with usage context. Tom if you allow, I suggest to look result screenshots on my site www.samtrest.com.
Thanks

More to Explore

PL/SQL demos

Check out more PL/SQL tutorials on our LiveSQL tool.

PL/SQL docs

PL/SQL reference manual from the Oracle documentation library