Dependencies
Displaying direct and Indirect dependencies
Step 1 – Run the script utldtree.sql that creates the objects that enable you to display the direct and indirect dependencies.
(This script is in ORACLE_HOME/rdbms/admin folder)
Scenario – Table is emp
EMP table has two direct dependent objects of type view and trigger.
create or replace view V_Emp
as
select * from emp
where job = 'MANAGER'
/
create or replace trigger E_Trig
after insert on emp
for each row
begin
dbms_output.put_line('Record inserted in emp table');
end;
/
There is a procedure P_V_Emp which is based on view V_Emp.
So there is indirect dependency of table emp with P_V_Emp procedure.
create or replace procedure P_V_Emp(no emp1.empno%type, name emp1.ename%type, j emp1.job%type, s emp1.sal%type DEFAULT 'MANAGER')
is
begin
Insert into V_Emp(empno,ename,job,sal)
values(no,name,j,s);
end;
/
Now there is a procedure P2 which calls P_V_Emp
create or replace procedure p2
is
begin
P_V_Emp(123,'abc', 8000);
end;
/
To get the list of dependencies execute the procedure deptree_fill
EXECUTE deptree_fill('TABLE','SCOTT','EMP');
DEPTREE VIEW
select nested_level, type, name
from deptree
/
To display an indented representation of all the dependent objects query ideptree view.
Discussion about this article
| Author | Body |
Rahul
8/29/2009 9:40 PM
|
Please provide feedback about this article here.
To participate in this discussion Sign up for free membership of 24x7code.
To Signup click on Login , Use create user link & the follow the instructions.
Thank you
|