Excellent ! ! ! ! !
Very very thanks !!!

SQL>
1 CREATE or replace PROCEDURE testproc IS
2 i PLS_INTEGER;
3 var_table varchar2(7):='TESTTAB';
4 cursor dummy is select * from TESTTAB;
5 BEGIN
6 execute immediate 'SELECT COUNT(*) FROM '||var_table||'' INTO i;
7 dbms_output.put_line(TO_CHAR(i));
8* END testproc;
9 /

Procedure created.

Elapsed: 00:00:00.09
SQL> exec dbms_utility.get_dependency('TABLE', 'DWHADMIN','TESTTAB');
-
DEPENDENCIES ON DWHADMIN.TESTTAB
------------------------------------------------------------------
*TABLE DWHADMIN.TESTTAB()
* PROCEDURE DWHADMIN.TESTPROC()

PL/SQL procedure successfully completed.

Elapsed: 00:00:00.05
SQL>

This is my purpose.

I want development one procedure to get all names of procedures that affect a table.

The result should be:

TABLE PROCEDURE
testproc proc_1 <-- Procedure
testproc proc_2 <-- Procedure
testproc func_1 <-- Function
testproc pkg_load_1 <-- Package


So, i would set up a hard dependency in all procedures,functions,packages with contain sql dynamic??

Thanks again.

Hi Tom,
During development, objects are compiled into the system using text files with "create <object>" syntax.

I would like to create a script that would order all of the objects in a schema so I can create a build script to rebuild into another schema.

I've pieced together this query:

select
lpad('.',1*(level-1),'.')||lower(name)||' --'||lower(type) objectname,level,
referenced_name,referenced_type*/
from user_dependencies
where/
referenced_name not in ('DBMS_OUTPUT','DUAL','STANDARD','HTP','SYS_STUB_FOR_PURITY_ANALYSIS',
'UTL_FILE','UTL_SMTP')
and referenced_type not in ('SEQUENCE','TABLE','NON-EXISTENT')
start with rownum=1
connect by nocycle prior name=referenced_name

and used dbms_utility.get_dependency but both solutions would involve multiple calls to an object that was already built.

Do you have a solution that would allow me to reverse-engineer all objects in a schema that I could use to create a concise build script?

Thanks,
ht

Hi Tom,
I would like to reverse-engineer the build order into the build scripts I maintain and are checked into source control.

Is there a method that allows me to list the objects in order that they have to be built without building the same object multiple times? The 2 methods I described are showing multiple entries for each object so the result would be a longer-than-needed build process.

Tom,
I attempted an expdp using "content=metadata_only include=PROCEDURE,VIEW" thinking that may give me what I need. Since there's a specific order in which the objects need to be built, it doesn't seem like this technique will work for me. Even if it did, is there a data dictionary query or package that will let me simply list all of a schema's objects in build sequence?

In other words, I would just like to generate a list of objects that I can then use with case statements to create a file with:
@obj1
@obj2
@obj3

Thanks.
ht

Hi Tom,
I've attached an example of the issue I'm trying to solve. By using the scott schema, I created 4 views and 3 stored procedures. With the help of the user_dependencies data dictionary view, I can display a hierarchy of the objects.

I would like to create a build script that creates the objects in order but am having problems with duplicate values.

Since my schema has a few thousand objects, I would like to eliminate the duplicate values and be able to run the build script once, in order, avoiding the need to run multiple builds. I can't use export/import because I'm maintaining source control on the build files and need to build using that reference.

Thanks in advance.


Connected.

drop view empv1;

View dropped.

drop view empv2;

View dropped.

drop view empv3;

View dropped.

drop view empv4;

View dropped.


create view empv1 as select * from emp;

View created.


create view empv2 as select * from empv1;

View created.


create view empv3 as select * from empv2;

View created.


create view empv4 as select * from empv2;

View created.


create or replace procedure p1
2 is
3 v1 empv3.empno%type;
4 begin
5 select empno into v1 from empv3 where rownum=1;
6 end;
7 /

Procedure created.


create or replace procedure p2
2 is
3 v1 emp.empno%type;
4 begin
5 select empno into v1 from emp where rownum=1;
6 end;
7 /

Procedure created.


create or replace procedure p3
2 is
3 v1 empv4.empno%type;
4 begin
5 select empno into v1 from empv4 where rownum=1;
6 end;
7 /

Procedure created.


select
2 lpad('.',7*(level-1),'.')||ud.name name
3 from
4 user_dependencies ud
5 start with rownum=1
6 connect by nocycle
7 prior trim(ud.name)=trim(ud.referenced_name)
8 and prior trim(ud.type)=trim(ud.referenced_type);

P2
EMPV1
.......EMPV2
..............EMPV4
.....................P3
..............EMPV3
.....................P1
EMPV2
.......EMPV4
..............P3
.......EMPV3
..............P1
EMPV4
.......P3
EMPV3
.......P1
P1
P3
P3
P1
P2
P3
P1
P2

24 rows selected.


select
2 (
3 case
4 when type='VIEW' then 'sqlplus @../views/'||ud.name||'.sql'
5 when type='PROCEDURE' then 'sqlplus @../procs/'||ud.name||'
.sql'
6 end
7 )buildscript
8 from
9 user_dependencies ud
10 start with rownum=1
11 connect by nocycle
12 prior trim(ud.name)=trim(ud.referenced_name)
13 and prior trim(ud.type)=trim(ud.referenced_type);

sqlplus @../procs/P2.sql
sqlplus @../views/EMPV1.sql
sqlplus @../views/EMPV2.sql
sqlplus @../views/EMPV4.sql
sqlplus @../procs/P3.sql
sqlplus @../views/EMPV3.sql
sqlplus @../procs/P1.sql
sqlplus @../views/EMPV2.sql
sqlplus @../views/EMPV4.sql
sqlplus @../procs/P3.sql
sqlplus @../views/EMPV3.sql
sqlplus @../procs/P1.sql
sqlplus @../views/EMPV4.sql
sqlplus @../procs/P3.sql
sqlplus @../views/EMPV3.sql
sqlplus @../procs/P1.sql
sqlplus @../procs/P1.sql
sqlplus @../procs/P3.sql
sqlplus @../procs/P3.sql
sqlplus @../procs/P1.sql
sqlplus @../procs/P2.sql
sqlplus @../procs/P3.sql
sqlplus @../procs/P1.sql
sqlplus @../procs/P2.sql

24 rows selected.

Hello Tom,
Often, developers come and ask me if it is possible to extract some rows of data from our production database in order to correct defect. Usually my answer is: I can't do that because of the numerous table dependencies. Say I want one row of the most used table and I have to retrieve rows from about 200+ tables to get the complete graph for this specific row or in other words to respect data integrity. Of course at the end, we have now a *full* copy of our production database so that developers (about 60) can re-copy this database (somewhat filtered) on their personal schema ending in development databases bigger than our production one.

My question is quite simple, is there a tool (SQL scripts, software...) that can achieve this goal? i.e. find dependencies (including cyclic) and extract all the data in a schema or a SQL file (containing inserts).

Thanks a lot in advance,
Loïc

Sir, could You kindly clarify that topic in respect of the "Enhanced Finer Grained Dependency Management in Oracle Database 11g Release 1" (and 11gR2).

I know that Oracle 11gR1 manages table dependencies for schema objects on column level. So that in case of "independent" column modification another objects (that do not depend on the modified column) stay valid.
But I cannot obtain column-level dependency data from dba_dependencies.
To my regret, I also was unable to find an answer for my question in the Documentation.
Please, explain the way to get the field-level dependency.