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
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 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 - the code in your reply worked great. The decode statement at the beginning is very clever - just the *magic* I needed.

:) 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

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.

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?

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

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

Oops.

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

Thanks again!
- J

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>;

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.
 

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>
))
/

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).

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,
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 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,

i meant user_arguments not user_procedures in the previous note

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.

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

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

</code> http://asktom.oracle.com/~tkyte/who_called_me/index.html <code>

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

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.

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.

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
/

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. :-)

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;
/

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.

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

Hi Tom,

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

Please advice

Thanks in advance.

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

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.

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