A reader, March 29, 2002 - 2:20 pm UTC
Need more info
Gerry Abreu, June 13, 2002 - 1:57 pm UTC
Hello,
The SQL is fine though I would need SQL for many more levels of dependencies not just two.
If you would please email me at gerry.abreu@eds.com if you have more info.
Thanks.
June 13, 2002 - 2:14 pm UTC
I would make a copy of user_dependencies then (for performance reasons mostly) and use connect by ( a sql clause -- see the sql language manual).
Just watch out for recursive dependencies ;)
question about USER_DEPENDENCIES
James Su, June 30, 2004 - 6:02 pm UTC
hi Tom,
Please tell me why I can see two NON-EXISTENT objects in USER_DEPENDENCIES (procedure WHO is compiled without errors)
Thank you!
SQL> select REFERENCED_NAME,REFERENCED_OWNER,REFERENCED_TYPE from USER_DEPENDENCIES WHERE NAME = 'WHO';
REFERENCED_NAME REFERENCED_OWNER REFERENCED_TYPE
------------------------------- ---------------------- -----------------
STANDARD SYS PACKAGE
DATABASE_PROPERTIES SYS VIEW
DATABASE_PROPERTIES PUBLIC SYNONYM
SYS_STUB_FOR_PURITY_ANALYSIS SYS PACKAGE
DBMS_OUTPUT SYS PACKAGE
DBMS_OUTPUT PUBLIC SYNONYM
DATABASE_PROPERTIES JAMES NON-EXISTENT
DBMS_OUTPUT JAMES NON-EXISTENT
8 rows selected.
June 30, 2004 - 8:16 pm UTC
they are anti-objects created when you reference a public synonym. for the invalidations/dependency to work -- we need something to hook onto. These non-existent (i call them anti-objects) provide that.
that way, if you were to create your own dbms_output -- the procedure dependent on the PUBLIC SYNONYM dbms_output would be identified and invalidated, your dbms_output object (james.dbms_output) would anhilate this anti-object and cause the invalidation.
Nice
Gerhard, May 11, 2005 - 1:25 pm UTC
Hello Tom,
While querying sys.dependency$ I find the columns namely
d_object#,p_object#
What do they represent??
I think as d_object# -> Dependent Object#
p_object# -> Parent object#
Am I right?
Any x$tables exist which provide information
about object dependencies??
I know about dba_dependencies and ideptree.
Any other views exist??
Please let me know.
Bye!
May 11, 2005 - 7:19 pm UTC
I don't know, never reversed engineered them, they are not documented.
Dependency?
Jairo Ojeda, July 15, 2005 - 11:43 am UTC
Tom, I need to list from a specific owner all tables with the same column name --like TIMESTAMP, and their dependencies because we need to rename it, but my query is very slow and dont get the tables with no dependencies. I have change the query to know the elapsed time: getting 1 row --3:30 min. but I have about 196 rows that match.
This is the base query I did:
SELECT dep.tree||obj.owner owner, obj.object_type, obj.object_name, obj.status
FROM DBA_OBJECTS obj,
(SELECT dep.object_id, RPAD('*',2*LEVEL,'*') tree
FROM PUBLIC_DEPENDENCY dep,
(SELECT object_id
FROM DBA_OBJECTS
WHERE owner = :owner AND
object_name = :object_name AND
object_type = :object_type) obj
CONNECT BY PRIOR dep.object_id = dep.referenced_object_id
START WITH dep.referenced_object_id = obj.object_id
ORDER SIBLINGS BY dep.object_id DESC) dep
WHERE dep.object_id = obj.object_id;
And this is what I tried:
SELECT dep.owner, dep.table_name, dep.tree||obj.owner dependency,
obj.object_type, obj.object_name, obj.status
FROM DBA_OBJECTS obj,
(SELECT RPAD('*',2*LEVEL,'*') tree, dep.object_id, obj.owner,
obj.table_name
FROM PUBLIC_DEPENDENCY dep,
(SELECT /*+ FIRST_ROWS */ *
FROM (SELECT /*+ FIRST_ROWS */ a.*, ROWNUM r
FROM (SELECT b.object_id, a.owner, a.table_name
FROM DBA_TAB_COLUMNS a, DBA_OBJECTS b
WHERE b.owner = a.owner AND
b.object_name = a.table_name AND
a.owner = :owner AND
b.object_type = :object_type AND
a.column_name = :column_name
ORDER BY a.table_name) A
WHERE ROWNUM <= :MAX)
WHERE r >= :MIN) obj
CONNECT BY PRIOR dep.object_id = dep.referenced_object_id
START WITH dep.referenced_object_id = obj.object_id
ORDER SIBLINGS BY dep.object_id DESC) dep
WHERE dep.object_id = obj.object_id;
July 15, 2005 - 6:03 pm UTC
this looks like a one time report.
rather than try to optimize anything, just copy of a dba_tab_columns those things with timestamp.
report them.
and then show a report of their dependents.
OK!
Jairo Ojeda, July 15, 2005 - 6:21 pm UTC
rename a column and
David, August 17, 2005 - 3:33 pm UTC
I need to rename a column of an important table that is referred in several pl sql procedures. Is there a query that could give me what are all the procedures/functions/triggers that are dependent on this column that I am going to rename? Thanks.
August 17, 2005 - 5:13 pm UTC
not on the column, it is not tracked to that level.
however, when you do this in TEST (because you will right.......), you'll find all of the procedures that go invlalid, you'll recompile them and the ones that don't compile -- they are the ones that relied on that column.
rename a column...
David, August 17, 2005 - 10:30 pm UTC
ok. if it is not tracked on the column level, at the least, is it possible to find using data dictionary, the dependent plsql procs/functions/triggers that refer this table? Thanks for your time.
August 18, 2005 - 3:39 pm UTC
dba|all|user_dependencies
SQl PLUS Querry
ranjeet kumar, September 10, 2005 - 3:28 am UTC
i want some query about the sql-plus and database querry
September 10, 2005 - 8:17 am UTC
ok?
Get columns too
Iain Hunter, November 28, 2005 - 11:30 am UTC
Thanks Tom, I modified your original query slightly as I found it useful to be able to see the dependant table AND associated column. Hope others find this useful:
SELECT UPPER(:table_name) "Parent Table",
iv.r_constraint_name "Parent Constraint",
(SELECT column_name FROM user_cons_columns WHERE constraint_name = iv.r_constraint_name) "Parent Column",
iv.table_name "Dependent Table",
iv.constraint_name "Constraint Name",
ucc.column_name "Dependent Column"
FROM user_cons_columns ucc,
(SELECT *
FROM user_constraints
WHERE r_constraint_name in (SELECT constraint_name
FROM user_constraints
WHERE table_name = UPPER(:table_name)
AND constraint_type IN ('P', 'U'))) iv
WHERE iv.constraint_name = ucc.constraint_name
Package dependencies
Vinayak, November 07, 2006 - 11:29 am UTC
Hi Tom,
I would like to write a query or package(if not possible in a query) that will list all the dependent objects of a package till the last level. User_dependencies gives us 1 level of dependency but I am unable to think of a way to call this recursively and I beleive this can be accomplished with a query using connect by clause.
An example would be:
PKG1 uses 2 tables(T1 & T2) and 2 packages (PKG2 & PKG3). PKG2 uses just 1 table(T3) and PKG3 uses 1 tables(T4) and 1 package PKG4. PKG4 uses 3 tables (T5,T6,T7).
My desired output would be a heirarchical display like:
PKG1
T1
T2
PKG2
T3
PKG3
T4
PKG4
T5
T6
T7
Thanks
Vinayak
November 07, 2006 - 4:41 pm UTC
yes, you can in fact do this readily and easily with connect by.
I'll let you give it the "old college try" first on your own - it won't hurt you.
getting an error
Vinayak, November 08, 2006 - 8:57 am UTC
Hi Tom,
Thanks for giving me a try.Here is the query that I wrote but it is giving "ORA-01436: CONNECT BY loop in user data".
select lpad(' ',2*(level-1)) || to_char(name) Objects
from user_dependencies
start with (name='PKG1' and referenced_name='PKG1')
connect by prior referenced_name=name
I understand this error is due to the fact that PKG1 occurs multiple times in the table. But don't know how to solve it.
here is the full script for the test:
create table T1 (a integer);
create table T2 (a integer);
create table T3 (a integer);
create table T4 (a integer);
create table T5 (a integer);
create table T6 (a integer);
create table T7 (a integer);
CREATE OR REPLACE package PKG4 IS
procedure do_nothing;
END PKG4;
/
CREATE OR REPLACE package body PKG4 IS
var1 integer;
var2 integer;
var3 integer;
procedure do_nothing IS
begin
select a into var1 from t5;
select a into var1 from t6;
select a into var1 from t7;
END;
END PKG4;
/
CREATE OR REPLACE package PKG3 IS
procedure do_nothing;
END PKG3;
/
CREATE OR REPLACE package body PKG3 IS
var1 integer;
procedure do_nothing IS
begin
select a into var1 from t4;
pkg4.do_nothing;
END;
END PKG3;
/
CREATE OR REPLACE package PKG2 IS
procedure do_nothing;
END PKG2;
/
CREATE OR REPLACE package body PKG2 IS
var1 integer;
procedure do_nothing IS
begin
select a into var1 from t3;
END;
END PKG2;
/
CREATE OR REPLACE package PKG1 IS
procedure do_nothing;
END PKG1;
/
CREATE OR REPLACE package BODY PKG1 IS
var1 integer;
var2 integer;
procedure do_nothing IS
begin
select a into var1 from t1;
select a into var2 from t2;
pkg2.do_nothing;
pkg3.do_nothing;
END;
END PKG1;
/
November 08, 2006 - 7:41 pm UTC
think about it....
look at your connect by (is connecting by NAME sufficient??)
look at your start with, you are starting with a package body, that connects to pkg1 the spec only.
you have not thought this through...
I have given it a chance !!!
Vinayak, November 13, 2006 - 10:58 am UTC
Tom,
Thanks for being a hawk-eyed mentor. I had tried using the old good emp heirarchy style. I have converted my data into the same way and processed it in the similar fashion and there it is. I have got the answer.
select lpad(' ',3*level-1) || to_char(empname) Objects,emptype Objecttype FROM(
select empname,emptype,mgrname,mgrtype from (
select 1,ud.REFERENCED_NAME empname,ud.REFERENCED_TYPE emptype,NULL mgrname,NULL mgrtype from user_dependencies ud where ud.REFERENCED_name='PKG1'
union all
select 2,ud.REFERENCED_NAME empname,ud.REFERENCED_TYPE emptype,ud.NAME mgrname,ud.TYPE mgrtype from user_dependencies ud where ud.REFERENCED_OWNER!='SYS'
and ud.REFERENCED_NAME!=ud.NAME
ORDER By 1))
start with mgrname is null
connect by prior empname=mgrname;
But Tom I myself is not convinced with this query. I beleive there must be a much better way to do this.But the problem is it works in my case but if I ran it in my actual development environment with actual package name, it again throughs the same error: ORA-01436: CONNECT BY loop in user data.
Please share what am I missing here.
November 14, 2006 - 4:05 am UTC
you could well have a recursive relationship that goes into an infinite loop. you could use "connect by nocycle" if you are using current software.
and please, the "key" of user dependencies is NOT just the name (that is the point, what is the "key" of user dependencies, you need to connect by THE KEY, not just part of it)
still unclear !!!!
Vinayak, November 14, 2006 - 6:15 am UTC
Tom, I am still unbale to understand the key that you are referring to. Please explain.
November 14, 2006 - 7:19 am UTC
ops$tkyte%ORA9IR2> desc user_dependencies
Name Null? Type
---------------------------------------- -------- ----------------------------
NAME NOT NULL VARCHAR2(30)
TYPE VARCHAR2(17)
REFERENCED_OWNER VARCHAR2(30)
REFERENCED_NAME VARCHAR2(64)
REFERENCED_TYPE VARCHAR2(17)
REFERENCED_LINK_NAME VARCHAR2(128)
SCHEMAID NUMBER
DEPENDENCY_TYPE VARCHAR2(4)
name+type
referenced_owner+referenced_name+referenced_type
those are two of the keys, you are connecting by -name, insufficient.
user_dependencies
Alejandro Vega, February 21, 2007 - 1:56 pm UTC
create table t(x number,y number);
Table created.
create or replace procedure pt1 is
l_n number;
begin
select count(*) into l_n from t;
end;
/
Procedure created.
create or replace procedure pt2 is
begin
pt1;
end;
/
Procedure created.
create or replace procedure pt3 is
begin
pt2;
end;
/
Procedure created.
column name format a10
column type format a10
column referenced_name format a25
column referenced_type format a25
select name,type,referenced_name,referenced_type
from user_dependencies
where referenced_name in ('T','PT1','PT2','PT3');
NAME TYPE REFERENCED_NAME REFERENCED_TYPE
---------- ---------- ------------------------- -------------------------
PT2 PROCEDURE PT1 PROCEDURE
PT3 PROCEDURE PT2 PROCEDURE
PT1 PROCEDURE T TABLE
3 rows selected.
select name,type,referenced_name,referenced_type
from user_dependencies
connect by prior trim(name) = trim(referenced_name) and
prior trim(type) = trim(referenced_type)
start with referenced_name = 'T';
no rows selected
create table t_user_dependencies as select * from user_dependencies;
Table created.
select name,type,referenced_name,referenced_type
from t_user_dependencies
connect by prior trim(name) = trim(referenced_name) and
prior trim(type) = trim(referenced_type)
start with referenced_name = 'T';
NAME TYPE REFERENCED_NAME REFERENCED_TYPE
---------- ---------- ------------------------- -------------------------
PT1 PROCEDURE T TABLE
PT2 PROCEDURE PT1 PROCEDURE
PT3 PROCEDURE PT2 PROCEDURE
3 rows selected.
If I use user_dependencies I don't see rows, but when I use t_user_dependencies I see rows.
Am I loosing anything or is it a bug ?
February 21, 2007 - 3:17 pm UTC
I cannot reproduce
ops$tkyte%ORA10GR2> select name,type,referenced_name,referenced_type
2 from user_dependencies
3 connect by prior trim(name) = trim(referenced_name) and
4 prior trim(type) = trim(referenced_type)
5 start with referenced_name = 'T';
NAME TYPE REFERENCED_NAME REFERENCED_TYPE
---------- ---------- ------------------------- -------------------------
PT1 PROCEDURE T TABLE
PT2 PROCEDURE PT1 PROCEDURE
PT3 PROCEDURE PT2 PROCEDURE
ops$tkyte%ORA10GR2>
ops$tkyte%ORA10GR2> create table t_user_dependencies as select * from user_dependencies;
Table created.
ops$tkyte%ORA10GR2>
ops$tkyte%ORA10GR2>
ops$tkyte%ORA10GR2>
ops$tkyte%ORA10GR2> select name,type,referenced_name,referenced_type
2 from t_user_dependencies
3 connect by prior trim(name) = trim(referenced_name) and
4 prior trim(type) = trim(referenced_type)
5 start with referenced_name = 'T';
NAME TYPE REFERENCED_NAME REFERENCED_TYPE
---------- ---------- ------------------------- -------------------------
PT1 PROCEDURE T TABLE
PT2 PROCEDURE PT1 PROCEDURE
PT3 PROCEDURE PT2 PROCEDURE
it looks like a "bug", you likely have different optimizer settings than I - so you will want to work this via support. I tried in my 9ir2 and 10gr2 databases and it worked OK in both.
table dependency in procedure inside package
A reader, February 26, 2008 - 12:38 pm UTC
Hi Tom,
Is it possible to find the dependency of a table for procedures inside a package ? I ran following query to get the list of package names but dont know how many procedures are using the table inside it.
SELECT * FROM USER_DEPENDENCIES WHERE REFERENCED_NAME in
( %LIST OF TABLE_NAME%)
AND TYPE IN ('PACKAGE BODY');
Thanks in advance,
February 26, 2008 - 3:10 pm UTC
not tracked at that that, only tracked at the schema object level, which is the package.
the package either uses it or not, that is all we care about.
How to find dependencies on Objects of Remote database
Bhavesh Adatiya, December 22, 2009 - 2:06 am UTC
We are missing something in this discussion. Just wanted to Add to it and would also like to know.
How can we find out depencenies on the Objects of a remote database. Let's assume I have created a view and i am referring to a table of Remote database using DB_LINK.
How can i find out these kind of dependencies. I tried to user DBA_DEPENDENCIES.
DBA_DEPENDENCIES describes all dependencies in the database between procedures, packages, functions, package bodies, and triggers, including dependencies on views created without any database links.
December 23, 2009 - 7:16 am UTC
Your object is dependent on the database link - and that is all.
I like to use views, as they import the metadata of the remote table and it is pretty clear what the view refers to to (single table view). It makes the compilation of the procedure a bit speedier as well since the procedure is dependent on the view, not on the database link behind the view - we don't need to even open the link to compile the procedure.
dba_dependencies referenced_link_name
Ulfet, February 15, 2010 - 4:39 am UTC
Hello Tom,
Could you tell me why some remote objects do not observe on dba_dependencies?
Let me explain my question.
When I select :
select * from dba_dependencies where name='PKG_PREPAID_HISTORY'; -- PKG_PREPAID_HISTORY is package and in pack body there are remote objects used
This result does not shows me remote object(s)
I know and I can see when I execute below SQL statement:
select * from dba_source where name='PKG_PREPAID_HISTORY' and owner='SUBDEALER' and lower(text) like lower('%@%') and rownum <3;
OWNER,NAME,TYPE,LINE,TEXT
SUBDEALER,PKG_PREPAID_HISTORY,PACKAGE BODY,20, FROM bgw_usr.adjustment_cs3@onsubs_airdr a, prepaid_history b
SUBDEALER,PKG_PREPAID_HISTORY,PACKAGE BODY,32, FROM bgw_usr.tickler_cs3@onsubs_airdr a, prepaid_history b
But when execute:
select * from dba_dependencies where name='PKG_PREPAID_HISTORY' and referenced_link_name is not null;
No rows returned!!!
Please describe me why above (at least) sql no shows?
Have a lot of packages, procedures etc which calls remote object but I can`t see them on dba_dependencies.
Oracle version: 10g
February 16, 2010 - 5:03 pm UTC
can you run a small test for me
ops$tkyte%ORA10GR2> create table t ( x int );
Table created.
ops$tkyte%ORA10GR2>
ops$tkyte%ORA10GR2> create database link ora10gr2@loopback connect to ops$tkyte identified by foobar using 'ora10gr2';
Database link created.
ops$tkyte%ORA10GR2> select * from dual@ora10gr2@loopback;
D
-
X
ops$tkyte%ORA10GR2> create or replace procedure p
2 as
3 begin
4 for x in ( select * from t@ora10gr2@loopback )
5 loop
6 dbms_output.put_line( x.x );
7 end loop;
8 end;
9 /
Procedure created.
ops$tkyte%ORA10GR2> select * from user_dependencies where name = 'P' and referenced_link_name is not null;
NAME TYPE REFERENCED_OWNER
------------------------------ ----------------- ------------------------------
REFERENCED_NAME
----------------------------------------------------------------
REFERENCED_TYPE
-----------------
REFERENCED_LINK_NAME
-------------------------------------------------------------------------------
SCHEMAID DEPE
---------- ----
P PROCEDURE OPS$TKYTE
T
TABLE
ORA10GR2.COM@LOOPBACK
592 HARD
*_DEPENDENCIES
Prakash, February 18, 2010 - 12:18 am UTC
Tom,
In USER_DEPENDENCIES we have DEPENDENCY_TYPE varchar2(4).
I often see 'HARD' value in it. What is this mean?
Is there any other values other than 'HARD'?
thanks,
Prakash
February 18, 2010 - 9:19 am UTC
ops$tkyte%ORA11GR2> select distinct dependency_type from dba_dependencies;
DEPE
----
HARD
REF
http://docs.oracle.com/docs/cd/E11882_01/server.112/e10820/statviews_1067.htm#REFRN20053 has the values listed as well.
ops$tkyte%ORA10GR2> create function f( x in number ) return varchar2
2 deterministic
3 as
4 begin
5 return to_char(x,'999999999');
6 end;
7 /
Function created.
ops$tkyte%ORA10GR2>
ops$tkyte%ORA10GR2> create table t ( x number );
Table created.
ops$tkyte%ORA10GR2> create index t_idx on t(f(x));
Index created.
ops$tkyte%ORA10GR2> create trigger t
2 before insert on t
3 begin
4 null;
5 end;
6 /
Trigger created.
ops$tkyte%ORA10GR2>
ops$tkyte%ORA10GR2> select name, type, referenced_name, referenced_type, dependency_type
2 from user_dependencies
3 /
NAME TYPE REFERENCED REFERENCED DEPE
----- ---------- ---------- ---------- ----
F FUNCTION STANDARD PACKAGE HARD
T_IDX INDEX F FUNCTION REF
T TRIGGER T TABLE HARD
Missing information in DBA_DEPENDENCIES
Shafi, August 13, 2010 - 5:51 am UTC
Hi Tom,
In our production database there are some procedures which use some remote tables but in DBA_DEPENDENCIES some of this tables are not shown. This missing information is so dangerous for us, because we have to know all dependencies. What can be reason of this missing information?
August 19, 2010 - 12:35 am UTC
I'd need to see a "for example", show me a valid plsql routine with missing information (keyword = valid)
Missing information in DBA_DEPENDENCIES
A reader, September 03, 2010 - 6:24 am UTC
This situation exist between 10g and 9i databases, in some circumstances such, let say, in db10g database user A has table X, and also in db9i A user has X table. If in db10g a procedure uses A.X@db9i table, this dependency is not shown in dba_dependencies.
Thank you in advance.
Missing information in DBA_DEPENDENCIES
A reader, September 03, 2010 - 6:32 am UTC
CREATE OR REPLACE procedure CMDB.syn_dupl
as
f integer;
begin
select t into f
from cmdb.x@db_link
where rownum = 1;
end;
/
select owner, name, referenced_owner, referenced_name from dba_dependencies where name = 'SYN_DUPL';
OWNER NAME REFERENCED_OWNER REFERENCED_NAME
CMDB SYN_DUPL SYS STANDARD
CMDB SYN_DUPL SYS SYS_STUB_FOR_PURITY_ANALYSIS
As shown cmdb.x@db_link table doesn't shown in dba_dependencies;
September 09, 2010 - 7:04 pm UTC
please utilize support - although if this a 9i specific issue, it is doubtful that unless there is a patch already that it would be/could be fixed.
Dpendency over Columns
A reader, April 12, 2011 - 5:59 am UTC
Hi Tom,
Can we track the dependency over the column used in specific views in Oracle 10G.
I saw we can track through DBA_DEPENDENCY_COLUMN in 11G but is there any possiblity in 10G.
I can see it through DBA_DEPENDENCIES but only tables. Could you help.
April 13, 2011 - 9:15 am UTC
That mapping did not exist prior to 11g when dependency tracking was pushed down to the column level
find call path from a_pkg to b_pkg
A reader, February 07, 2012 - 11:41 pm UTC
Hi, Tom,
I know the a_pkg call b_pkg via sql trace file.
But how to find its call path?
From this thread, I know the information is stored in dba_depencencies.
But the difficulties are:
1. using connect by prior referenced_owner=owner and prior referenced_name=name and prior referenced_type=type will not work because 'package' and 'package body' types may both reference a 'package' type with same owner and name.
2. after i changed connect by with some like prior referenced_owner=owner and prior referenced_name=name
and (prior referenced_type='PACKAGE' and type in ('PACKAGE BODY','PACKAGE') or prior referenced_type!='PACKAGE' and prior referenced_type=type), it shows ora-1436 to me.
3. then i use connect by nocycle, but it returns too many rows that the level is more than 100(i am using an EBS database).
Could you please give me your SQL for my reference?
Thanks,
William
February 08, 2012 - 1:39 am UTC
From this thread, I know the information is stored in dba_depencencies.
not really, that would show all possible static paths (there could be a huge number of those), but it would never show the dynamic (at runtime) paths - using dynamic sql calls.
we do not keep this information anywhere - except at runtime. At runtime, b_pkg could log, if it wanted to, the call stack that resulted in its being called.
short of that, we don't track it - and you cannot really get it from the *_dependencies tables. I don't know how you deduced this from a trace file either - the trace file shows SQL and dynamically executed PL/SQL, but not every function call made.
I don't have any sql - since you cannot really extract this information.
Split based on Column Value
Veera, February 08, 2012 - 3:15 am UTC
I have a table like follows
Name Gender
-------------
Arun M
Anitha F
Bala M
Banu F
I need the output as follows
Male Female
-----------------
Arun Anitha
Bala Banu
What are the ways can we generate the above query (Please provide any Advanced SQL concepts)
Thanks
Veera
February 08, 2012 - 5:09 am UTC
I've assumed
a) there could be more M than F's or vice versa
b) you want the output sorted alphabetically in each column
c) you want two columns - there is no spacing, hard to say
in the future, provide create tables and inserts - make it EASY for the person you are asking a question of to provide an answer.
ops$tkyte%ORA11GR2> create table t ( nm varchar2(10), gender varchar2(1) );
Table created.
ops$tkyte%ORA11GR2>
ops$tkyte%ORA11GR2> insert into t values ( 'Arun','M' );
1 row created.
ops$tkyte%ORA11GR2> insert into t values ( 'Anitha','F' );
1 row created.
ops$tkyte%ORA11GR2> insert into t values ( 'Bala','M' );
1 row created.
ops$tkyte%ORA11GR2> insert into t values ( 'Banu','F' );
1 row created.
ops$tkyte%ORA11GR2> insert into t values ( 'Tom','M' );
1 row created.
ops$tkyte%ORA11GR2>
ops$tkyte%ORA11GR2> with
2 male as (select nm, row_number() over (order by nm) rn from t where gender = 'M'),
3 female as (select nm, row_number() over (order by nm) rn from t where gender = 'F')
4 select male.nm, female.nm
5 from male full outer join female on (male.rn = female.rn)
6 order by coalesce( male.rn, female.rn)
7 /
NM NM
---------- ----------
Arun Anitha
Bala Banu
Tom
find call path from a_pkg to b_pkg
A reader, February 08, 2012 - 4:32 am UTC
Hi, Tom,
Thank you for your reply.
I know if there is any call from 'dynamic sql call' we have no information about it in dba_dependencies.
But I know there is no dynamic call in the path from a_pkg to b_pkg. So, the information in dba_dependencies may work.
Why I need to know the call path from a_pkg to b_pkg is because we found something wrong(e.g. some very strange update statement which should not appear) in the trace file.
Then we want to comfirm if b_pkg is called by a_pkg indeed and what's the calling path so that we can analysis the main calling logic(it's too complex of a_pkg to read all the codes of it).
So, I need your help to write a sql to find out the calling path and cover my 3 difficulties in my last post.
Thanks,
William
February 08, 2012 - 5:19 am UTC
But I know there is no dynamic call in the path from a_pkg to b_pkg. So, the
information in dba_dependencies may work.and it might not, and it could provide many paths from A to B, some of them infinite in size, and .. and .. and ...
this will print out a tree
select rpad('*',2*level,'*')||referenced_owner||'.'||referenced_name||'.'||referenced_type nm, name, type
from user_dependencies
start with referenced_name = 'PKG_A' and referenced_type = 'PACKAGE' and referenced_owner = user
connect by prior referenced_owner = user and referenced_name = prior name and referenced_type = prior type
/
you might need to throw in some NOCYCLE logic to prevent infinite loops, you'll probably want to stop connecting if you hit "PKG_B"
need help depedencies
A reader, March 26, 2013 - 8:24 pm UTC
Hey Tom , I'm working on Oracle developper , I need help , well I need to create a table WORK_ODI_DBA_DEPENDENCIES that import data from dba_dependencies , I dont know why my procedure doesn't work , it show me : Source does not have a runnable target /// plz does anyone know why ?
here is my code :
----------------------------------------
CREATE OR REPLACE PROCEDURE PRC_ALIMENTERDEP IS
BEGIN
delete from WORK_ODI_DBA_DEPENDENCIES;
insert into WORK_ODI_DBA_DEPENDENCIES (referenced_owner, referenced_name, type, name)
select
referenced_owner, referenced_name, type, name
from
SELECT referenced_owner, referenced_name, type, name from dba_dependencies WHERE
referenced_owner IN ('ODS', 'DWH') and referenced_type = 'TABLE';
COMMIT;
EXCEPTION
WHEN OTHERS THEN
ROLLBACK;
END PRC_ALIMENTERDEP;
----------------------------------
I need to create this table to use it as source table in ODI
Thank u in advance ^^
March 27, 2013 - 5:11 pm UTC
EXCEPTION
WHEN OTHERS THEN
ROLLBACK;
I hate your code - hate it, simply despise it. I would never let it into production in a billion years. Just upon seeing that.
asktom.oracle.com/Misc/when-others-then-null-redux.html
http://asktom.oracle.com/Misc/dreaded-others-then-null-strikes-again.html How I wish plsql did not have rollback and commit.... sigh... just an all around bad practice.
that said.
I have no idea why your code doesn't work, "Source does not have
a runnable target" means nothing to me.
perhaps if you got rid of that exception block so we could see the ACTUAL ERROR MESSAGE - we'd know.
the problem with your code is that it ALWAYS SUCCEEDS, even when it fails. Think about it - if you run this code - the thing calling this code - can it determine if
a) this code succeeded
b) this code failed?
actually - it can - it can determine that this code succeeds 100% of the time, it can never fail - because of that horrible when others "logic" you have there.