Skip to Main Content
  • Questions
  • All Parent - Child tables in the database

Breadcrumb

Question and Answer

Chris Saxon

Thanks for the question, Srinivas.

Asked: May 17, 2001 - 11:15 am UTC

Last updated: May 11, 2018 - 1:05 pm UTC

Version: 8.x

Viewed 50K+ times! This question is

You Asked

Hi Tom,

Can you please explain the way to get a list of all parent child relation in the database.

The list should have the Grand parent as the first item and the last item will be the grand child.

For Example,

Parent Child
----------------------------------------------------------------
BDP_INFO BAA
CONTR ABP
CONTR B_INCE
CONTR BDP_INFO


BDP_INFO is the parent to BAA and also Child to CONTR. So the list should have
Contr ---------> BDP_info
BDP_info ------> BAA

like wise...

This list should be for all tables that i have access

and we said...

as long as you DO NOT have any self referencing integrity:

create table emp ( empno int primary key, mgr int references emp(empno) );

this will work:


tkyte@TKYTE816> create table p ( x int primary key );

Table created.

tkyte@TKYTE816> create table c1 ( x primary key references p );

Table created.

tkyte@TKYTE816> create table c2 ( x primary key references c1 );

Table created.

tkyte@TKYTE816> create table c3 ( x primary key references c2 );

Table created.

tkyte@TKYTE816> create table c4 ( x primary key references c2 );

Table created.

tkyte@TKYTE816>
tkyte@TKYTE816>

--
-- Addenda: Oct 2018
-- Thanks to Stew Ashtom for this update
--

with pur as (
  select table_name, constraint_type, constraint_name, r_constraint_name,
    max(decode(constraint_type,'R',1,0)) over(partition by table_name) is_r
  from user_constraints 
  where constraint_type in ('P', 'U', 'R')
)
, son_dad as (
  select distinct s.table_name son, d.table_name dad, d.constraint_type
  from (select * from pur where constraint_type = 'R' or is_r = 0) s
  left join pur d
    on s.r_constraint_name = d.constraint_name
    and s.table_name != d.table_name
)
select level lvl, son, dad, constraint_type
from son_dad
start with dad is null
connect by dad = prior son
order siblings by dad, son;
 


Rating

  (37 ratings)

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

Comments

All Parent - Child tables in the database

Srinivas, May 17, 2001 - 3:08 pm UTC

Yep, that's what i wanted, thanks a lot for the solution !!!

How we can extend this table to generate dependancy tree?

VJ, May 17, 2001 - 4:39 pm UTC

Any example/idea on generating Dependancy tree including PL/sql packages, procedures etc.(in fact, all the possible objects)?

How can I derive DDL from the Data Dictionary ?

Andre Whittick Nasser, October 15, 2001 - 9:50 am UTC

Thanks for your tip, very useful.

Now, Tom, I would be very glad if you could provide me with tips for writing a script that derives DDLs like CREATE TABLE, etc, possibly for all objects, from the D.D.

I know I can use EXP to get this info, but I'd like to format my own output.

If I am not mistaken, there is a script somewhere in Oracle's site with something similar. Do have the link ?

Thanks again !

Tom Kyte
October 15, 2001 - 4:27 pm UTC

I'm not personally aware of such a script -- I myself use EXP (actually, I use exp ONLY to extract other peoples schema's cause I keep my scripts up to date and never need to extract the ddl)...

In any case, 9i has a feature you'll like:

scott@ORA9I.WORLD> select dbms_metadata.get_ddl( 'TABLE', 'EMP' ) from dual;

DBMS_METADATA.GET_DDL('TABLE','EMP')
--------------------------------------------------------------------------------

CREATE TABLE "SCOTT"."EMP"
( "EMPNO" NUMBER(4,0) NOT NULL ENABLE,
"ENAME" VARCHAR2(10),
"JOB" VARCHAR2(9),
"MGR" NUMBER(4,0),
"HIREDATE" DATE,
"SAL" NUMBER(7,2),
"COMM" NUMBER(7,2),
"DEPTNO" NUMBER(2,0),
CONSTRAINT "EMP_PK" PRIMARY KEY ("EMPNO")
USING INDEX PCTFREE 10 INITRANS 2 MAXTRANS 255
STORAGE(INITIAL 524288 NEXT 524288 MINEXTENTS 1 MAXEXTENTS 2147483645 PCTINCRE
ASE 0
FREELISTS 1 FREELIST GROUPS 1 BUFFER_POOL DEFAULT) TABLESPACE "USERS" ENABLE,

CONSTRAINT "EMP_FK_EMP" FOREIGN KEY ("MGR")
REFERENCES "SCOTT"."EMP" ("EMPNO") ENABLE NOVALIDATE,
CONSTRAINT "EMP_FK_DEPT" FOREIGN KEY ("DEPTNO")
REFERENCES "SCOTT"."DEPT" ("DEPTNO") ENABLE NOVALIDATE
) PCTFREE 10 PCTUSED 40 INITRANS 1 MAXTRANS 255 LOGGING
STORAGE(INITIAL 524288 NEXT 524288 MINEXTENTS 1 MAXEXTENTS 2147483645 PCTINCRE
ASE 0
FREELISTS 1 FREELIST GROUPS 1 BUFFER_POOL DEFAULT) TABLESPACE "USERS"



Thanks, anyway...

Andre Whitick Nasser, October 16, 2001 - 9:21 am UTC

I'm aware of 9i's DDL extraction feature. But the only problem is I need to format the output my own way. Thanks again for the eye-opener, reminding me to keep the scripts of all my work. In fact, that is something I always do. And I have a complete (somewhat) organized library for that purpose.

However, nothing is perfect, and every now and then I have to roll up my sleeves and do a "reverse engeneering" on somebody else's database that does not have this "healthy" (i.e. necessary) habit. Besides, I need to format the scripts according to the company's needs .

Hence my question...

Thanks again !

RAN INTO the following error

Famiy, March 19, 2002 - 5:51 pm UTC

ERROR at line 2:
ORA-01436: CONNECT BY loop in user data

I tried to use your solution and ran into the above error.

and

cant we give a filtering condition in a hierarchial query
i.e.for example if I want to use your solution only for tables starting with 'HEDGE'

Tom Kyte
March 19, 2002 - 7:43 pm UTC

do you have any self referencing integrity? If you do -- this'll not work (as I said in the answer)....


Yes, you can filter a connect by

where tname like 'HEDGE%'
...
start with tname like 'HEDGE%'
...
connect by prior ..... and tname like 'HEDGE%'
....

But, it might be easier to just copy the info for tables like HEDGE% in the first place. But as I said, if a table refers to itself, you have a connect by loop and this will NOT work

Why are you inserting into the table twice

Famiy, March 19, 2002 - 5:58 pm UTC

once you are inserting all the tablnames and constraints with constraint 'P'

and what are you inserting the second time.

Can you provide with some explanation.

Tom Kyte
March 19, 2002 - 7:44 pm UTC

first I insert primary keys (constraint_type = 'P')

Then I insert all primary keys along with the foreign keys that point to them (constraint_type = 'R')

Recursive PL/SQL

Mark J. Bobak, March 20, 2002 - 2:40 am UTC

I did something similar, but I implemented it in a recursive
PL/SQL routine. This way, I was able to check for loops due
to self-referencing tables.

I don't have the code here at home, but could probably post
it tomorrow if folks are interested. I have a LIST_CHILDREN
and a LIST_PARENTS.

-Mark

What to do with self referencing Tables

Vishal Sharma, January 09, 2003 - 5:23 pm UTC

Hi Tom,
As U are the most reliable and last resort for questions. Regarding this question of all parent -child tables. I have two tables which are self referencing. I want to get a list of all other tables in parent child manner and can take of these two tables seperately. how should i make my query run for rest of all the tables excluding those 2 say table1 and table2.

Tom Kyte
January 09, 2003 - 7:08 pm UTC

add a couple of where clauses to exclude them i suppose?

Excluding pig's ears

A P Clarke, January 10, 2003 - 6:05 am UTC

Tom

I think it's quite simple to exclude self-referential foreign keys that use the table's primary key:

select a.table_name,
a.constraint_name pkey_constraint,
b.constraint_name fkey_constraint,
b.r_constraint_name
from user_constraints a, user_constraints b
where a.table_name = b.table_name
and a.constraint_type = 'P'
and b.constraint_type = 'R'
and a.constraint_name != b.r_constraint_name
/

...unless I am missing something.

Your query doesn't pick up foreign key relationships that use unique keys other than primary keys. Do you regard that as bad practice?

Cheers, APC

Tom Kyte
January 10, 2003 - 7:32 am UTC

don't see it as "bad", just unusual. you could add them by adding a decode to a.constraint_type making P and U "the same"

Let's say you DO have self-referential integrity

Doug, January 22, 2004 - 4:33 pm UTC

Tom - if you DO have self - referenetial integrity, and say it's only a couple of tables, how you can you cleanly remove those rows in temp_constraints so that the output at least functions, although it is missing a description of the self-referential stuff.

Tom Kyte
January 22, 2004 - 8:29 pm UTC

go for it! consider it a challenge!

(in 10g, i'd use "NOCYCLE" on the connect by :)

think about it -- a recusive relationship - when do you stop?

if you want to do this in 9i and before, you'll be writing procedural code to process the data.

"NOCYCLE" emulation in pre-10g

Matthias Rogel, March 17, 2004 - 5:11 am UTC

also annoyed by ORA-01436 and not yet upgraded to 10g,
this one may be helpful.

input: any query resulting in a parent/child-relationship
output: all paths in the resulting graph, stopping at cycles
(optionally no sub-path is delivered, "only the longest")

with it you can for example

query your Parent-Child-Table-Hierarchy

select * from table(hierarchical.allpaths(cursor(select a.table_name as parent, b.table_name as child from user_constraints a, user_constraints b where a.constraint_name = b.r_constraint_name and a.constraint_type = 'P' and b.constraint_type = 'R')))

( try out
create table temp1 (t1 integer primary key, t3 integer);
create table temp2(t2 integer primary key, t1 references temp1(t1));
create table temp3(t3 integer primary key, t2 references temp2(t2));
alter table temp1 add constraint tc1 foreign key(t3) references temp3(t3);
and let the query run)

or query your object-dependeny-hierarchy
select * from table(hierarchical.allpaths(cursor(select referenced_name as parent, name as child from user_dependencies where referenced_owner=user)));

note that 2 temp tables are needed.
note further that the in_list_number-function I learnt from
this site.

here we go

create global temporary table allpcr_h(
entity varchar2(4000) unique,
allpcr_h_id integer primary key)
on commit delete rows;

create sequence allpcr_h_seq;

CREATE TRIGGER allpcr_h_ID
BEFORE INSERT ON allpcr_h
FOR EACH ROW
begin
select allpcr_h_seq.nextval
into :new.allpcr_h_ID
from dual;
end;
/

create global temporary table allpcr(
pid integer,
cid integer,
path varchar2(4000))
on commit delete rows;


create type numbertable is table of number;
/

create type longstringtable is table of varchar2(32767);
/


create package hierarchical is
function in_list_number(vinstring in varchar2) return numbertable;

type CPType is record(
parent varchar2(512),
child varchar2(512));

type CPRType is REF Cursor return CPType;

function AllPaths(vcp in CPRType,
vonlylongest in integer default 1, -- if set to 0, all paths are returned
vseparator in varchar2 default ' -> ')
return longstringtable pipelined;
end hierarchical;
/

create package body hierarchical is

function in_list_number(vinstring in varchar2) return numbertable is
l_string long default vinstring || ',';
l_data numbertable := numbertable();
n number;
begin
loop
exit when l_string is null;
n := instr( l_string, ',' );
l_data.extend;
l_data(l_data.count) :=
ltrim( rtrim( substr( l_string, 1, n-1 ) ) );
l_string := substr( l_string, n+1 );
end loop;
return l_data;
end in_list_number;


function AllPaths(vcp in CPRType,
vonlylongest in integer,
vseparator in varchar2) return longstringtable pipelined is
pragma autonomous_transaction;
cp CPType;
hnt numbertable;
i integer;
ret varchar2(32767);
begin
loop
fetch vcp into cp;
exit when vcp%notfound;

insert into allpcr_h(entity)
(select cp.parent from dual
union all select cp.child from dual)
minus select entity from allpcr_h;

insert into allpcr(pid, cid, path)
select hp.allpcr_h_id, hc.allpcr_h_id, hp.allpcr_h_id || ',' || hc.allpcr_h_id
from allpcr_h hp, allpcr_h hc
where hp.entity = cp.parent
and hc.entity = cp.child
minus select pid, cid, path from allpcr;
end loop;

close vcp;

loop
insert into allpcr(pid, cid, path)
select ap.pid, ac.cid, ap.pid || ',' || ac.path
from allpcr ap, allpcr ac
where ap.cid = ac.pid
and (ap.pid, ac.cid) in
(select ap.pid, ac.cid
from allpcr ap, allpcr ac
where ap.cid = ac.pid
minus
select pid, cid
from allpcr);
exit when sql%rowcount = 0;
end loop;


if vonlylongest != 0 then
delete allpcr ad
where exists
(select 1
from allpcr aa
where aa.rowid != ad.rowid
and (aa.path like '%,' || ad.path
or aa.path like ad.path || ',%'));
end if;


for allpcr_rec in (select * from allpcr) loop
hnt := in_list_number(allpcr_rec.path);
ret := null;
for i in 1 .. hnt.count loop
if ret is null then
select entity
into ret
from allpcr_h
where allpcr_h_id = hnt(i);
else
select entity || vseparator || ret
into ret
from allpcr_h
where allpcr_h_id = hnt(i);
end if;
end loop;
pipe row(ret);
end loop;

rollback;

return;
end AllPaths;

end hierarchical;
/




Bug in HTML-DB ?

Matthias Rogel, March 18, 2004 - 5:26 am UTC

funny, just played around with my test-account
on </code> http://htmldb.oracle.com/pls/otn/development_service_home <code>

(

select * from v$version

SQL Query Results
Output to Excel
BANNER
Oracle9i Enterprise Edition Release 9.2.0.3.0 - Production
PL/SQL Release 9.2.0.3.0 - Production
CORE 9.2.0.3.0 Production
TNS for Linux: Version 9.2.0.3.0 - Production
NLSRTL Version 9.2.0.3.0 - Production
1-5

Statement took 0.02 seconds.



when I try the statement I posted
select * from table(hierarchical.allpaths(cursor(select a.table_name as parent,
b.table_name as child from user_constraints a, user_constraints b where
a.constraint_name = b.r_constraint_name and a.constraint_type = 'P' and
b.constraint_type = 'R')))

I get a
Not Found
The requested URL /pls/otn/wwv_flow.accept was not found on this server.

Oracle HTTP Server Powered by Apache/1.3.22 Server at htmldb.oracle.com Port 80


I have no problems using sqlplus on my local 9-er dbs.

Tom Kyte
March 18, 2004 - 8:25 am UTC

Matthias,

Thank you for reporting this.

This is, in fact, a bug in the underlying database that is running HTML DB and not a defect in HTML DB itself.

The bug, 3055525, is specific to a pipelined function returning an object type, and this aborts the database session. This problem is specific to Linux and specific to 9.2.0.3 (which is what is currently running on htmldb.oracle.com). This problem will be corrected on htmldb.oracle.com when the site is upgraded to production 10g.

I reproduced this problem using SQL*Plus on this instance...thus removing HTML DB from the picture.

Thanks again.

Joel

Developer

Developer, June 24, 2004 - 11:47 am UTC

I need to truncate all the tables in our database. How can I find the correct table order list to truncate without oracle ORA-02266: unique/primary keys in table referenced by enabled foreign keys

Thanks


Tom Kyte
June 24, 2004 - 3:05 pm UTC

there will not be a correct order -- you cannot truncate a parent table with active fkeys regardless.

see the "enabled foreign keys" -- it doesn't know or care if any of the child tables have rows, it only knows and cares that there are enabled fkeys.


ops$tkyte@ORA9IR2> create table t1 ( a int primary key );
 
Table created.
 
ops$tkyte@ORA9IR2> create table t2 ( a references t1 );
 
Table created.
 
ops$tkyte@ORA9IR2>
ops$tkyte@ORA9IR2>
ops$tkyte@ORA9IR2> truncate table t1;
truncate table t1
               *
ERROR at line 1:
ORA-02266: unique/primary keys in table referenced by enabled foreign keys
 
 <b>
so, you'll have to disable, truncate in any old order, enable</b>


ops$tkyte@ORA9IR2>
ops$tkyte@ORA9IR2>
ops$tkyte@ORA9IR2> begin
  2      for x in ( select 'alter table ' || table_name ||
  3                        ' disable constraint ' || constraint_name  stmt
  4                   from user_constraints
  5                  where constraint_type = 'R' )
  6      loop
  7          dbms_output.put_line( x.stmt );
  8          execute immediate x.stmt;
  9      end loop;
 10      for x in ( select 'truncate table ' || table_name stmt
 11                   from user_tables )
 12      loop
 13          dbms_output.put_line( x.stmt );
 14          execute immediate x.stmt;
 15      end loop;
 16      for x in ( select 'alter table ' || table_name ||
 17                        ' enable constraint ' || constraint_name  stmt
 18                   from user_constraints
 19                  where constraint_type = 'R' )
 20      loop
 21          dbms_output.put_line( x.stmt );
 22          execute immediate x.stmt;
 23      end loop;
 24  end;
 25  /
alter table T2 disable constraint SYS_C007718
truncate table T1
truncate table T2
alter table T2 enable constraint SYS_C007718
 
PL/SQL procedure successfully completed.
 
 

Developer

Develoepr, June 28, 2004 - 10:15 am UTC

Yes. It is a big help.

Why did disconnection occur?

gaperumal@indiatimes.com, September 23, 2004 - 6:32 am UTC

Hi Tom,
 I am trying to use your Code. But my communication channel disconnected. What could be the reason? 

SQL> conn scott/tiger
Connected.
SQL> begin
  2          for x in ( select 'alter table ' || table_name ||
  3                            ' disable constraint ' || constraint_name  stmt
  4                       from user_constraints
  5                      where constraint_type = 'R' )
  6          loop
  7              dbms_output.put_line( x.stmt );
  8              execute immediate x.stmt;
  9          end loop;
 10         for x in ( select 'truncate table ' || table_name stmt
 11                      from user_tables )
 12         loop
 13             dbms_output.put_line( x.stmt );
 14             execute immediate x.stmt;
 15         end loop;
 16         for x in ( select 'alter table ' || table_name ||
 17                           ' enable constraint ' || constraint_name  stmt
 18                      from user_constraints
 19                     where constraint_type = 'R' )
 20         loop
 21             dbms_output.put_line( x.stmt );
 22             execute immediate x.stmt;
 23         end loop;
 24     end;
 25  /
begin
*
ERROR at line 1:
ORA-03113: end-of-file on communication channel 


SQL> spool off

Thank you 

Tom Kyte
September 24, 2004 - 8:11 am UTC

3113 = please contact support for assistance.

Original Response

Bill C., November 30, 2004 - 8:34 pm UTC

Your first response to this question was just what I needed tonight. Thanks for keeping all this good stuff recorded for all of us!

Seems odd that you can't get table hierarchy out of an existing Oracle view. Seems Oracle would only need to augment user_dependencies or something like that.

Tom Kyte
November 30, 2004 - 9:01 pm UTC

in 10g, there is "NOCYCLE".....

one more clarification

Raajesh, January 27, 2005 - 7:50 am UTC

Tom

Can u tell me how to achieve the following

I have a table structure like this

field 1 field 2

X Null
Y X
Z X
A Y
B Y
C Z
D Z
I A
J B
K C
L D

Now, given the input as X. I want to get all the childs under X. In this case, I want
X,Y,Z,A,B,C,D,I,J,K,L

If it is 'J' I want J alone.Since it is not parent for anything.(identified by field 2)

i.e. i have to boil down to the lowest child of all the elements into an array

Is there a way to achieve this using single SQL or PL SQL..

rgds
srr

Tom Kyte
January 27, 2005 - 9:54 am UTC

when I find this "u" person, I'll ask them. "U" does seem to get requested by name alot. I have yet to meet "u" however.... Do you know where "u" is?

but until "u" is found, try this:

select * from t start with field1 = :x connect by prior field1 = field2;

not able to actually test it, since there was no create table/insert intos -- but it looks correct.



thanks

raajesh, January 27, 2005 - 2:46 pm UTC

U are great...

Market hierarchy and product in one row

Elahe, February 03, 2005 - 3:53 pm UTC

Hi Tom,

I am working on DW project. There are two dimensions DW_TBL_DIM_PRODUCT and DW_TBL_DIM_MARKET.
Product dimension includes all information about product and the primary key for this table is SRL which is a sequence.
CREATE TABLE DW_TBL_DIM_PRODUCT
(
SRL NUMBER(10) NOT NULL,
DIN VARCHAR2(10),
BRAND_DESC VARCHAR2(50),
BRAND_LAUNCH_DATE DATE,
MANUF_ID VARCHAR2(10),
MANUFACTURER VARCHAR2(100),
)
Market dimension includes all market information and primary key for this table is SRL which is sequence. There is a parent-child relationship between markets. For each market which acts as both parent and child there is MARKET_PAR_ID with not null value and for those which they are at the highest level market structure or they are just parents MARKET_PAR_ID is null.
As I have created a denormalized data model, instead of recursive relationship between parent and child I have all data elements for each market in one row.

CREATE TABLE DW_TBL_DIM_MARKET
(
SRL NUMBER(10),
PK_MARKET NUMBER(10),
MARKET_ID VARCHAR2(30),
MARKET_DESC VARCHAR2(50),
MARKET_PAR_ID VARCHAR2(30),
MARKET_PAR_DESC VARCHAR2(50),
MARKET_CLASS NUMBER(1),
MARKET_LEVEL NUMBER(1),
)
In order to show each product belongs to which market I have a fact less fact table
DW_TBL_PRODUCT_MARKET:
CREATE TABLE DW_TBL_PRODUCT_MARKET
(
FK_DIM_PRODUCT NUMBER(10) NOT NULL,
FK_DIM_MARKET NUMBER(10) NOT NULL,
PK_PRODUCT_MARKET NUMBER(10) NOT NULL,
SOURCE_IND VARCHAR2(3),
)
FK_DIM_PRODUCT is a foreign key referring to DW_TBL_DIM_PRODUCT.SRL and FK_DIM_MARKET is a foreign key referring to DW_TBL_DIM_MARKET.SRL.

There 6 markets (at parent level) so market_class has values from 1 to 6.
Under each market there are sub-markets (parent-child) which their market_level (0,1,2,3) for those that they are parents market_level =0.

Currently, the maximum level of some markets is 2 and for some other is 3, however they may change; it means we may want to consider more or less level of sub-markets.

We are using Cognos to create report for business and I was asked for a view including all product and market information in one row.

I created this view for them, but it is not dynamic and if we add level 4 to the market structure I need to update this view manually.

CREATE OR REPLACE VIEW DW_VIW_PRODUCT_MARKET
AS
SELECT
MARKET_CLASS,
PAR0.MARKET_DESC MARKET,
PAR1.MARKET_DESC GROUP1,
PAR2.MARKET_DESC GROUP2,
'NOT_USED' GROUP3,
p. BRAND_DESC,
P.SRL FK_DIM_PRODUCT
FROM
DW_TBL_PRODUCT_MARKET MP,
DW_TBL_DIM_PRODUCT P,
DW_TBL_DIM_MARKET PAR2,
DW_TBL_DIM_MARKET PAR1,
DW_TBL_DIM_MARKET PAR0
WHERE
MP.FK_DIM_PRODUCT = P.SRL
AND MP.FK_DIM_MARKET = PAR2.SRL
AND PAR2.MARKET_PAR_ID = PAR1.MARKET_ID
AND PAR1.MARKET_PAR_ID = PAR0.MARKET_ID
AND PAR2.CUR_FLAG_IND = 'Y'
AND P.CUR_FLAG_IND = 'Y'
AND PAR2.MARKET_LEVEL = 2
UNION
SELECT
MARKET_CLASS,
PAR0.MARKET_DESC MARKET,
PAR1.MARKET_DESC GROUP1,
PAR2.MARKET_DESC GROUP2,
PAR3.MARKET_DESC GROUP3,
p. BRAND_DESC,
P.SRL FK_DIM_PRODUCT
FROM
DW_TBL_PRODUCT_MARKET MP,
DW_TBL_DIM_PRODUCT P,
DW_TBL_DIM_MARKET PAR3,
DW_TBL_DIM_MARKET PAR2,
DW_TBL_DIM_MARKET PAR1,
DW_TBL_DIM_MARKET PAR0
WHERE MP.FK_DIM_PRODUCT = P.SRL
AND MP.FK_DIM_MARKET = PAR3.SRL
AND PAR3.MARKET_PAR_ID = PAR2.MARKET_ID
AND PAR2.MARKET_PAR_ID = PAR1.MARKET_ID
AND PAR1.MARKET_PAR_ID = PAR0.MARKET_ID
AND PAR3.CUR_FLAG_IND = 'Y'
AND P.CUR_FLAG_IND = 'Y'
AND PAR3.MARKET_LEVEL = 3;

I am wondering if you can give me better idea of creating this view in a way that I don’t need to update it manually?


Market Parent_Market
======================
X Null
Y X
Z X
A Y
B Y
C Z
D Z
I A
J B
K C
L D

I want to get all the childs under X. In this case, I want is:

X,Z,D,L, product1
X,Z,C,K, product2


Thanks,

Elahe



Tom Kyte
February 04, 2005 - 1:27 am UTC

maybe ask this as a new question when I'm taking them, too big to do here and no inserts, etc to work with (please don't put them here, reviews/followups are something i glance at, this is too big to glance at)

product, market and sub-market in one row

Elahe, February 04, 2005 - 1:38 pm UTC

Hi Tom,
I have three tables:

CREATE TABLE DW_TBL_DIM_PRODUCT
SRL NUMBER(10) NOT NULL,
BRAND_DESC VARCHAR2(50);

Insert into DW_TBL_DIM_PRODUCT values (1, prd1);
Insert into DW_TBL_DIM_PRODUCT values (2, prd2);
Insert into DW_TBL_DIM_PRODUCT values (3, prd3);
Insert into DW_TBL_DIM_PRODUCT values (4, prd4);
Insert into DW_TBL_DIM_PRODUCT values (5, prd5);
Insert into DW_TBL_DIM_PRODUCT values (6, prd6);
Insert into DW_TBL_DIM_PRODUCT values (7, prd7);
Insert into DW_TBL_DIM_PRODUCT values (8, prd8);
Insert into DW_TBL_DIM_PRODUCT values (9, prd9);

CREATE TABLE DW_TBL_DIM_MARKET
SRL NUMBER(10) NOT NULL,
MARKET_DESC VARCHAR2(50),
MARKET_PAR_SRL VARCHAR2(30),
MARKET_PAR_DESC VARCHAR2(50),
MARKET_CLASS NUMBER(1),
MARKET_LEVEL NUMBER(1);

Insert into DW_TBL_DIM_MARKET values (1, ‘PPPAB’, null, null, 1, 0)
Insert into DW_TBL_DIM_MARKET values (2, ‘PPEF’, null, null, 2, 0)
Insert into DW_TBL_DIM_MARKET values (3, ‘PPG’, null, null, 3, 0)
Insert into DW_TBL_DIM_MARKET values (4, ‘PPH’, null, null, 4, 0)
Insert into DW_TBL_DIM_MARKET values (5, ‘PPPCD’, null, null, 5, 0)

Insert into DW_TBL_DIM_MARKET values (6, ‘PPA’, 1, ‘PPPAB’, 1, 1)
Insert into DW_TBL_DIM_MARKET values (7, ‘PPB’, 1, ‘PPPAB’, 1, 1)
Insert into DW_TBL_DIM_MARKET values (8, ‘PEF’, 2, ‘PPEF’, 1, 1)
Insert into DW_TBL_DIM_MARKET values (9, ‘PG’, 3, ‘PPG’, 1, 1)
Insert into DW_TBL_DIM_MARKET values (10, ‘PH’, 4, ‘PPH’, 1, 1)
Insert into DW_TBL_DIM_MARKET values (11, ‘PPCD’, 5, ‘PPPCD’, 1, 1)

Insert into DW_TBL_DIM_MARKET values (12, ‘PA’, 6, ‘PPA’, 1, 2)
Insert into DW_TBL_DIM_MARKET values (13, ‘PB’, 7, ‘PPB’, 1, 2)
Insert into DW_TBL_DIM_MARKET values (14, ‘E’, 8, ‘PEF’, 1, 2)
Insert into DW_TBL_DIM_MARKET values (15, ‘F’, 8, ‘PEF’, 1, 2)
Insert into DW_TBL_DIM_MARKET values (16, ‘G’, 9, ‘PG’, 1, 2)
Insert into DW_TBL_DIM_MARKET values (17, ‘H’, 10, ‘PH’, 1, 2)
Insert into DW_TBL_DIM_MARKET values (18, ‘PC’, 11, ‘PPCD’, 1, 2)
Insert into DW_TBL_DIM_MARKET values (19, ‘PD’, 11, ‘PPCD’, 1, 2)

Insert into DW_TBL_DIM_MARKET values (20, ‘A’, 12, ‘PA’, 1, 3)
Insert into DW_TBL_DIM_MARKET values (21, ‘B’, 13, ‘PB’, 1, 3)
Insert into DW_TBL_DIM_MARKET values (22, ‘C’, 18, ‘PC’, 1, 3)
Insert into DW_TBL_DIM_MARKET values (23, ‘D’, 19, ‘PD’, 1, 3)


CREATE TABLE DW_TBL_PRODUCT_MARKET
FK_DIM_PRODUCT NUMBER(10) NOT NULL,
FK_DIM_MARKET NUMBER(10) NOT NULL;

Insert into DW_TBL_PRODUCT_MARKET values (1,20)
Insert into DW_TBL_PRODUCT_MARKET values (2,21)
Insert into DW_TBL_PRODUCT_MARKET values (3,14)
Insert into DW_TBL_PRODUCT_MARKET values (4,15)
Insert into DW_TBL_PRODUCT_MARKET values (5,16)
Insert into DW_TBL_PRODUCT_MARKET values (6,17)
Insert into DW_TBL_PRODUCT_MARKET values (7,17)
Insert into DW_TBL_PRODUCT_MARKET values (8,22)
Insert into DW_TBL_PRODUCT_MARKET values (9,23)

This is what I want:

MARKET_CLASS MARKET SUB_MARKET1 SUB_MARKET2 SUB_MARKET3 PRODUCT
1 PPPAB PPA PA A prd1
1 PPPAB PPB PB B prd2
2 PPEF PEF E NOT_USED prd3
2 PPEF PEF F NOT_USED prd4
3 PPG PG G NOT_USED prd5
4 PPH PH H NOT_USED prd6
4 PPH PH H NOT_USED prd7
5 PPPCD PPCD PC C prd8
5 PPPCD PPCD PD D prd9

Thanks,

Elahe



Tom Kyte
February 04, 2005 - 2:20 pm UTC

good, give it a go. (what does this have to do with the question that was asked in the thread?)

showing parent-child in a row joing with another table!

A reader, February 04, 2005 - 2:42 pm UTC

Hi Tom,

Sorry for my cause of confusion. This time I tried to make my question more understandable and readable. HTH


CREATE TABLE DW_TBL_DIM_PRODUCT
(
SRL NUMBER(10) NOT NULL,
BRAND_DESC VARCHAR2(50)
)
/

Insert into DW_TBL_DIM_PRODUCT values (1, 'prd1');
Insert into DW_TBL_DIM_PRODUCT values (2, 'prd2');

CREATE TABLE DW_TBL_DIM_MARKET
(
SRL NUMBER(10) NOT NULL,
MARKET_DESC VARCHAR2(50),
MARKET_PAR_SRL VARCHAR2(30),
MARKET_PAR_DESC VARCHAR2(50),
MARKET_CLASS NUMBER(1),
MARKET_LEVEL NUMBER(1)
);




Insert into DW_TBL_DIM_MARKET values (1, 'PPPAB', null, null, 1, 0);
Insert into DW_TBL_DIM_MARKET values (6, 'PPA', 1, 'PPPAB', 1, 1);
Insert into DW_TBL_DIM_MARKET values (7, 'PPB', 1, 'PPPAB', 1, 1);
Insert into DW_TBL_DIM_MARKET values (12, 'PA', 6, 'PPA', 1, 2);
Insert into DW_TBL_DIM_MARKET values (13, 'PB', 7, 'PPB', 1, 2);
Insert into DW_TBL_DIM_MARKET values (20, 'A', 12, 'PA', 1, 3);
Insert into DW_TBL_DIM_MARKET values (21, 'B', 13, 'PB', 1, 3);


Insert into DW_TBL_DIM_MARKET values (2, 'PPEF', null, null, 2, 0);
Insert into DW_TBL_DIM_MARKET values (8, 'PEF', 2, 'PPEF', 1, 1);
Insert into DW_TBL_DIM_MARKET values (14, 'E', 8, 'PEF', 1, 2);
Insert into DW_TBL_DIM_MARKET values (15, 'F', 8, 'PEF', 1, 2);


CREATE TABLE DW_TBL_PRODUCT_MARKET
(
FK_DIM_PRODUCT NUMBER(10) NOT NULL,
FK_DIM_MARKET NUMBER(10) NOT NULL
);

Insert into DW_TBL_PRODUCT_MARKET values (1,20);
Insert into DW_TBL_PRODUCT_MARKET values (2,21);

SELECT
LPAD(' ',10*(LEVEL-1)) || market_desc market_tree
FROM dw_tbl_dim_market
START WITH market_desc = 'PPPAB'
CONNECT BY PRIOR srl = market_par_srl;

MARKET_TREE
----------------------------------------------
PPPAB
PPA
PA
A
PPB
PB
B
7 rows selected.

*Now my question is how I can show the result in two rows than 7 rows including their products like this:


1 PPPAB PPA PA A prd1
1 PPPAB PPB PB B prd2


Many thanks again for your time,
Elahe


All Parent - Child tables in the database

charlie shao, September 29, 2005 - 3:49 pm UTC

Your method will require building a temp tables, which is a hassel, we can do it in a sql:

select rpad( '*', (level-1)*4, '*' ) || table_name table_name
from (select a.table_name, a.constraint_name pkey_constraint,
b.constraint_name fkey_constraint, null r_constraint_name
from dba_constraints a, dba_constraints b
Where a.owner=upper('dm_infoed_administrator')
and a.owner=b.owner and a.table_name=b.table_name
and a.constraint_type='P' and b.constraint_type='R'
Union All
select distinct t1.table_name child_table,null pkey_constraint,
t1.constraint_name fkey_constraint,
t3.constraint_name r_constraint_name
from sys.dba_constraints t1,
sys.dba_constraints t2,dba_constraints t3
Where
t2.Table_Name = t3.Table_Name
and t3.constraint_type='R' and
t1.r_owner = &1
and t1.constraint_type = 'R'
and t1.r_constraint_name = t2.constraint_name
and t1.r_owner = t2.owner)
start with pkey_constraint is not null
connect by prior fkey_constraint = r_constraint_name

Tom Kyte
September 30, 2005 - 8:41 am UTC

in version 8.x when this was written

you cannot connect by a join.


things change....

Is there a way to detect cycle using trigger?

Kim, December 20, 2005 - 3:05 am UTC

Can we detect a cycle during insert/update using a trigger?

I have:

create table org_structure (
emp_id varchar2(10) not null,
mgr_id varchar2(10)
);

insert into org_structure values ('001', null);
insert into org_structure values ('002', '001');
insert into org_structure values ('003', '002');
insert into org_structure values ('005', '003');
insert into org_structure values ('004', '003');
insert into org_structure values ('002', '005'); <-- cycle form !!!

How can we detect this and prevent the insert/update from taking place? I tried a trigger by selecting value of NOCYCLE, but got hit with mutating table problem.



Tom Kyte
December 20, 2005 - 9:08 am UTC

You have to SERIALIZE access to the table of course, so you would absolutely need to use a LOCK TABLE command.

When you want to verify anything that "crosses rows" in a single table OR "crosses tables" in a database - you will be SERIALIZING.  

Once you accept that, you would have to utilize the "three trigger approach"

A before trigger to reset a set of package variables and to lock the table.
An after, for each row trigger to capture the modified values
An after trigger to iterate over the values you just captured to validate the data.

something like this (verify it, just sort of banged it out).

Think about why the lock table might be necessary (in a multi-user enviroment).

After seeing it, you might say "hmm, perhaps we shouldn't let applications insert directly into this table, this would make a really nice stored procedure without the complexity and hidden magic of triggers running in the background..."  still need the lock table, but the complexity goes way way 
WAY down...


ops$tkyte@ORA9IR2> create table org_structure (
  2    emp_id    varchar2(10)    not null,
  3    mgr_id    varchar2(10)
  4  );
 
Table created.
 
ops$tkyte@ORA9IR2>
ops$tkyte@ORA9IR2> create or replace package state_pkg
  2  as
  3      type array is table of rowid index by binary_integer;
  4      g_data array;
  5
  6      procedure do_it;
  7  end;
  8  /
 
Package created.
 
ops$tkyte@ORA9IR2> create or replace package body state_pkg
  2  as
  3
  4  procedure do_it
  5  is
  6      l_cnt     number;
  7  begin
  8      for i in 1 .. g_data.count
  9      loop
 10          select count(*) into l_cnt
 11            from ( select *
 12                     from org_structure
 13                    start with (rowid = g_data(i))
 14                  connect by prior emp_id = mgr_id
 15                    union all
 16                   select *
 17                     from org_structure
 18                    start with (rowid = g_data(i))
 19                  connect by prior mgr_id = emp_id);
 20      end loop;
 21  end do_it;
 22
 23  end;
 24  /
 
Package body created.
 
ops$tkyte@ORA9IR2> create or replace trigger org_structure_BI before insert on org_structure
  2  begin
  3      lock table org_structure in exclusive mode;
  4      state_pkg.g_data.delete;
  5  end;
  6  /
 
Trigger created.
 
ops$tkyte@ORA9IR2>
ops$tkyte@ORA9IR2> create or replace trigger org_structure_AIFER after insert on org_structure
  2  for each row
  3  begin
  4      state_pkg.g_data( state_pkg.g_data.count+1 ) := :new.rowid;
  5  end;
  6  /
 
Trigger created.
 
ops$tkyte@ORA9IR2>
ops$tkyte@ORA9IR2>
ops$tkyte@ORA9IR2> create or replace trigger org_structure_AI after insert on org_structure
  2  begin
  3      state_pkg.do_it;
  4  end;
  5  /
 
Trigger created.
 
ops$tkyte@ORA9IR2> insert into org_structure values ('001', null);
 
1 row created.
 
ops$tkyte@ORA9IR2> insert into org_structure values ('002', '001');
 
1 row created.
 
ops$tkyte@ORA9IR2> insert into org_structure values ('003', '002');
 
1 row created.
 
ops$tkyte@ORA9IR2> insert into org_structure values ('005', '003');
 
1 row created.
 
ops$tkyte@ORA9IR2> insert into org_structure values ('004', '003');
 
1 row created.
 
ops$tkyte@ORA9IR2> insert into org_structure values ('002', '005');
insert into org_structure values ('002', '005')
            *
ERROR at line 1:
ORA-01436: CONNECT BY loop in user data
ORA-06512: at "OPS$TKYTE.STATE_PKG", line 10
ORA-06512: at "OPS$TKYTE.ORG_STRUCTURE_AI", line 2
ORA-04088: error during execution of trigger 'OPS$TKYTE.ORG_STRUCTURE_AI'
 
 

How would I write a query for this?

Sakhi, December 20, 2005 - 12:43 pm UTC

Hi Tom,

I have a query as
select col1,col2 from a
union all
select col1, col2 from b;

Here a and b are not related.
But I now the relationship has changed such that every row in B is a child of A and A is the parent. Every row in A may or may not have a row in B.

How can I get same output as the above query output with the new set of tables?

How can this be achieved in single a query?

Tom Kyte
December 20, 2005 - 1:11 pm UTC

er? insufficient data. I don't understand the question at all.

I understand "parent/child"

I don't get how they relate to a union all view.

Nor do I see what the keys are between A and B.

Nor do I see what these new tables look like, so getting an equivalent query, well, that is not possible.




Foreign keys

Kubilay, April 28, 2006 - 5:39 am UTC

Hello Tom

I want to ask a design question today.

I have been working as a DBA for the last few years and I have
been involved in Java application support / development projects.

Whenenver I joined a team, first thing I usually looked for was a
"Data Model" an ER diagramme of the database schemas involved and
I could hardly find one. I want to get a big picture of what's in
the database and I am having difficulties in grasping the 'world of
interest' the database is trying to picture, without it.

For example, it is very hard to go through 190 tables in a schema
which have in average about 20 columns each and try to understand
the relationships beteween them and lets not mention the cryptic
names these tables might have.

Even when I take on the task of using:
select dbms_metadata.get_ddl( 'TABLE', 'sometable' ) from dual;
and getting a description of their constraints and try to figure out
the relationships myself, I end up seeing 'phantom constraints'
like

create table a
af1 number primary key,
af2 char(1)
/

create table b
bf1 number primary key,
bf2 char(1)
/

create table c
cf1 number primary key,
fk_af1 number not null,
fk_bf1 number, --manually named field by developer not an actual defined --constraint, ie not in USER_CONSTRAINTS ?
cf3 char(1)
CONSTRAINT "fk_af1" FOREIGN KEY ("fk_af1") REFERENCES a ("af1") ENABLE
/

So, fk_bf1 is not a foreign key, right?

I hit many tables like these occasionally with half defined constraints.
I tried to reverse engineer stuff by using ER tools but these
tools are based on assumptions that Primary key and Foreign key relationships
are sound and defined and are not able to generate me a nice ER diagramme.

I am aware of tools like Oracle Designer, but I don't know its capabilities with this
situation of manually (or maybe programatically, by developer ) defined foreign keys
outside the integrity checking of the database.

Is the fk_bf1 a foreign key in this case? Is it correct design?
Shall I include it in my hand designed ER diagramme?

Many thanks for all your help.

By the way your new book on 10g architecture rocks!

I like your quote "Applications come and go, but database stays"!

Regards

Kubilay

Tom Kyte
April 28, 2006 - 12:19 pm UTC

fk_bf1 is not a foreign key unless and until there is a constraint stating so, until then it is just a number.


This is such a sad posting to read, makes me so sad...

I cannot tell you if fk_bf1 should or should not be included - I have no idea, only the people that own the data would apparently know :(

Parent Child Tables

Parag J Patankar, July 31, 2006 - 2:03 am UTC

Hi Tom,

I had tried to execute your script mentioned above to list all parent child tables of a user on 9.2 db, I am getting following error

  1  select rpad( '*', (level-1)*2, '*' ) || table_name table_name
  2  from temp_constraints
  3  start with fkey_constraint is null
  4* connect by prior pkey_constraint = r_constraint_name
11:24:01 SQL> /
ERROR:
ORA-01436: CONNECT BY loop in user data



no rows selected

It because of may be self referencing integrity, How can I track down such tables or how to exlude such tables while inserting temp_constraints tables ?

thanks & regards
pjp 

Tom Kyte
July 31, 2006 - 8:07 am UTC

we can filter them out easily in the connect by (even in 8i)

ops$tkyte@ORA9IR2> create table dept
  2  ( deptno number constraint dept_pk primary key )
  3  ;
 
Table created.
 
ops$tkyte@ORA9IR2> create table emp
  2  ( empno number constraint emp_pk primary key,
  3    deptno constraint emp_fk_to_dept references dept,
  4    mgr constraint emp_fk_to_self references emp
  5  );
 
Table created.
 
ops$tkyte@ORA9IR2> create table projects
  2  ( proj_id number constraint proj_pk primary key,
  3    empno constraint proj_fk_to_emp references emp
  4  )
  5  /
 
Table created.
 
ops$tkyte@ORA9IR2> column relation format a20
ops$tkyte@ORA9IR2>
ops$tkyte@ORA9IR2> with temp_constraints
  2  as
  3  (
  4  select table_name, constraint_name pkey_constraint, null fkey_constraint, null r_constraint_name
  5    from user_constraints
  6   where constraint_type = 'P'
  7   union all
  8  select a.table_name,
  9         a.constraint_name pkey_constraint,
 10         b.constraint_name fkey_constraint,
 11         b.r_constraint_name
 12    from user_constraints a, user_constraints b
 13   where a.table_name = b.table_name
 14     and a.constraint_type = 'P'
 15     and b.constraint_type = 'R'
 16  )
 17  select rpad( '*', (level-1)*2, '*' ) || table_name relation
 18    from temp_constraints
 19   start with fkey_constraint is null
 20  connect by pkey_constraint <> r_constraint_name AND prior pkey_constraint = r_constraint_name
 21  /
 
RELATION
--------------------
DEPT
**EMP
****PROJECTS
EMP
**PROJECTS
PROJECTS
 
6 rows selected.
 
 

Tables without PK neither FK

Alvaro Varela De Marco, February 15, 2008 - 8:35 am UTC

Hi Tom,

If exists some table without PK it not appears in this query. I added it with an outer join using USER_TABLES.
Bear in mind that with this added table, the "BIN$..." tables are not in the result set anymore.

Look for "... FROM user_tables b LEFT OUTER JOIN user_constraints a ON a.table_name = b.table_name..." in the following modified query:


WITH temp_constraints AS
(
SELECT   b.table_name
        ,a.constraint_name pkey_constraint
        ,NULL fkey_constraint
        ,NULL r_constraint_name
FROM     user_tables b LEFT OUTER JOIN user_constraints a 
                        ON a.table_name = b.table_name
                        AND a.constraint_type = 'P'
UNION ALL
SELECT a.table_name
      ,a.constraint_name pkey_constraint
      ,b.constraint_name fkey_constraint
      ,b.r_constraint_name
FROM   user_constraints a, user_constraints b
WHERE  a.table_name = b.table_name
AND    a.constraint_type = 'P'
AND    b.constraint_type = 'R')
SELECT     RPAD ('*' , (LEVEL - 1) * 2 ,'*') || table_name relation
FROM       temp_constraints
START WITH fkey_constraint IS NULL
CONNECT BY pkey_constraint <> r_constraint_name
AND        PRIOR pkey_constraint = r_constraint_name
/

How do I generate an insert statement out of the first question in this review

Umesh Kasturi, April 26, 2010 - 7:46 am UTC

Hi Tom
The first question in the post gives us a list of all the Master - Detail tables in the schema. But I have a tricky requirement where in the team wants to generate an insert statement for a test criteria so that the data can be reproduced and used on to another test schema. please help

To explain
suppose there is a DEPT_NAME=ACCOUNTING
The query should list down the insert statements so that I get the details of ACCOUNTING, then get all employees from the EMP table related to the ACCOUNTING, if there is a salgrade related to it , get that also

In short I want a set of Master Detail data to be got to insert into all the parent children

Thanks
Tom Kyte
April 26, 2010 - 9:06 am UTC

if you code that, you can sell it - it is something tons of people want developed.

http://www.google.com/search?hl=en&source=hp&q=data+subsetting+tools&btnG=Google+Search&aq=f&oq=&aqi=&sourceid=Mozilla-search

Column(s) missing in dba_constraints

Abel, July 06, 2010 - 3:04 pm UTC

Hello, Tom,
when we create a FK, we must provide the affected column(s); however, this information does not appear in DBA_CONSTRAINTS. Is it essential to have the DDL of the table to retrieve this information?

Best regards,

Abel

Tom Kyte
July 06, 2010 - 4:47 pm UTC

dba_cons_columns has them

you can also use dbms_metadata to extract any ddl

Knowing all the tables which refererence a single table

Kumar, October 14, 2010 - 7:21 am UTC

Hi Tom ,
I am very much impressed with the exact answers that you are providing for our queries .

It would be great if you can let us know how to know all the tables that refer to a single table for its foreign key .

eg.
suppose I have a table PINCODE . There are many tables which have a foreign keys referencing this table . I need to know the table names .
Tom Kyte
October 15, 2010 - 7:51 am UTC

ops$tkyte%ORA11GR2> create table p ( x int primary key );

Table created.

ops$tkyte%ORA11GR2> create table c1 ( x references p );

Table created.

ops$tkyte%ORA11GR2> create table c2 ( x references p );

Table created.

ops$tkyte%ORA11GR2>
ops$tkyte%ORA11GR2> select owner, table_name
  2    from dba_constraints
  3   where (r_owner, r_constraint_name) in
  4         (select owner, constraint_name
  5                from dba_constraints
  6                   where table_name = 'P'
  7                     and owner = USER
  8                     and constraint_type = 'P')
  9  /

OWNER                          TABLE_NAME
------------------------------ ------------------------------
OPS$TKYTE                      C2
OPS$TKYTE                      C1

KISS

peasant, November 02, 2011 - 12:05 pm UTC

as always Tom makes things much more complicated than they need to be. He just keeps going on and on. Stick to the point!
Tom Kyte
November 02, 2011 - 12:12 pm UTC

well, that is just about the stupidest comment I've seen today. Congratulations! You win.

Solution to find all hierarchial tree of Parent-Child

Larisa, November 06, 2011 - 11:47 pm UTC

Hi Tom,
I've looked through these posts of "All Parent - Child tables in the database" and put my solution to find all hierarchial tree of Parent-Child.
In case if you need to find hierarchial tree from given Parent just change START WITH like:
start WITH table_name = ('TABLE_NAME') AND constraint_type = 'P'. Tested in Oracle 11.2.
What do you think?

SELECT LEVEL, constraint_name, constraint_type TYPE, table_name, r_constraint_name, parent_table_name, delete_rule, status,
CONNECT_BY_ROOT table_name Main_Table,
SYS_CONNECT_BY_PATH(table_name, '->') "Path"
FROM
( SELECT constraint_name, constraint_type, table_name, r_constraint_name,
(SELECT table_name
FROM user_constraints
WHERE constraint_name = t1.r_constraint_name) parent_table_name,
delete_rule, status
FROM user_constraints t1
WHERE constraint_type IN ('R','P')
)
start WITH constraint_type = 'P'
connect BY PRIOR table_name = parent_table_name;

copy the contents from book a to book b

Ahmed, September 22, 2013 - 12:08 pm UTC

Hi tom.
I have a book_contents table.
The structure of it is, as follows:
both of Edition_no, part_no are primary key,
content_no, content_text are the code and text of the content line,
parent_content_no is self foreign key for content_no to indicate the parent of the content as book titles as subtitles.

Some book contents are the same as another one. So I need to copy the contents from book a to book b.
How can I do that?

Hierarchical query works locally but gives illegal use of LONG data type when used with DBlink

Pranav, October 17, 2013 - 12:04 pm UTC


This works wonderfully on local
------------------------------
select
level as trank, table_name, column_name as col_name, r_table_name as parent_table,
r_column_name as parent_col
from (
SELECT uc.table_name, uc.constraint_name,
cols.column_name,
(select table_name from all_constraints
where constraint_name = uc.r_constraint_name and owner='SCOTT')
r_table_name,
(select column_name from all_cons_columns
where constraint_name = uc.r_constraint_name and position = cols.position and owner='SCOTT')
r_column_name, uc.constraint_type
FROM all_constraints uc
inner join all_cons_columns cols
on uc.constraint_name = cols.constraint_name
where constraint_type != 'C'
and uc.owner='SCOTT' and cols.owner='SCOTT')
where constraint_type = 'R'
start with table_name in ('DEPT')
connect by nocycle
prior table_name = r_table_name;


Querying Remote DB via DBLink fails (gives ORA-00997: Illegal use of LONG datatype error)
Only difference between this & earlier is @DBLINK1 for all dbviews
-----------------
select
level as trank, table_name, column_name as col_name, r_table_name as parent_table,
r_column_name as parent_col
from (
SELECT uc.table_name, uc.constraint_name,
cols.column_name,
(select table_name from all_constraints@dblink1
where constraint_name = uc.r_constraint_name and owner='SCOTT')
r_table_name,
(select column_name from all_cons_columns@dblink1
where constraint_name = uc.r_constraint_name and position = cols.position and owner='SCOTT')
r_column_name, uc.constraint_type
FROM all_constraints@dblink1 uc
inner join all_cons_columns@dblink1 cols
on uc.constraint_name = cols.constraint_name
where constraint_type != 'C'
and uc.owner='SCOTT' and cols.owner='SCOTT')
where constraint_type = 'R'
start with table_name in ('DEPT')
connect by nocycle
prior table_name = r_table_name;


I am using 11gR2 databases. Please suggest.

Thanks
Pranav

master detail 10g form

Areej, March 31, 2016 - 8:18 am UTC

in master detail block between two table
I run form,, the master table display only the data of the master table but the detail table doesn't display the data
It just display the id in detail table and the other data on detail table not display, whats the wrong? ...
i tried many but i doesn't work with me
how can i display the both table data?

how can i solve this???? please,
thanks
Connor McDonald
March 31, 2016 - 10:07 am UTC

For the detail block, check that the block table definition is correctly set (which I assume it is, since you are seeing the item).

For each item in the detail block, check that the database column is correctly set, ie, that it is a database item, and bound to a database column. Because Forms uses this to build a query for the detail block to send to the database.

Also, check that your relation between the two blocks is correctly defined. If the appropriate foreign keys were not present in the database, the relation may be missing or incorrect.

If Child Tables don't have a primary key defined they are missed by the provided solution.

Glen Gerber, May 08, 2018 - 5:16 pm UTC

This can be corrected with the following query.

CLEAR

CREATE GLOBAL TEMPORARY TABLE temp_constraints
AS
SELECT a.table_name
,a.constraint_name pkey_constraint
,b.constraint_name fkey_constraint
,b.r_constraint_name
FROM user_constraints a
,user_constraints b
WHERE 1 = 0
/

ALTER TABLE temp_constraints MODIFY fkey_constraint NULL;
ALTER TABLE temp_constraints MODIFY pkey_constraint NULL;

DELETE FROM temp_constraints;

INSERT INTO temp_constraints
SELECT table_name
,constraint_name
,NULL
,NULL
FROM user_constraints
WHERE constraint_type = 'P'
/*AND regexp_like(table_name, '^add_your_filter_here_if_you_wish_to_limit_it_to_a_set_of_tables$')*/;

INSERT INTO temp_constraints
SELECT c.table_name
,p.constraint_name pkey_constraint
,c.constraint_name fkey_constraint
,c.r_constraint_name
FROM user_constraints c
LEFT OUTER JOIN user_constraints p ON p.table_name = c.table_name
AND p.constraint_type = 'P'
WHERE c.constraint_type = 'R'
/

SELECT rpad('*', (LEVEL - 1) * 2, '*') || table_name table_name
FROM temp_constraints
START WITH fkey_constraint IS NULL
CONNECT BY PRIOR pkey_constraint = r_constraint_name
/

DROP TABLE temp_constraints;
Connor McDonald
May 11, 2018 - 5:42 am UTC

nice input thanks

Still need TEMP table? What about UNIQUE?

Stew Ashton, May 11, 2018 - 8:06 am UTC

I don't see the need for a temp table with more recent versions. Also, foreign keys can relate to UNIQUE keys, not just primary keys.
Note: tables that cannot be parents (because no primary or unique key) are omitted. Also, self_referencing foreign keys are ignored.
with pur as (
  select table_name, constraint_type, constraint_name, r_constraint_name,
    max(decode(constraint_type,'R',1,0)) over(partition by table_name) is_r
  from user_constraints 
  where constraint_type in ('P', 'U', 'R')
)
, son_dad as (
  select distinct s.table_name son, d.table_name dad, d.constraint_type
  from (select * from pur where constraint_type = 'R' or is_r = 0) s
  left join pur d
    on s.r_constraint_name = d.constraint_name
    and s.table_name != d.table_name
)
select level lvl, son, dad, constraint_type
from son_dad
start with dad is null
connect by dad = prior son
order siblings by dad, son;

LVL SON       DAD       C
--- --------- --------- -
  1 B                    
  1 DEPT                 
  2 EMP       DEPT      P
  1 P                    
  2 C         P         U
  1 PARENT               
  2 CHILD     PARENT    P
  1 T                    
  1 TAB1                 
  2 TAB2      TAB1      P
  2 TAB3      TAB1      P
  2 TAB4      TAB1      P
  2 TAB5      TAB1      P
  1 U                    
  1 VEHICLE              
  2 CAR       VEHICLE   P

Best regards, Stew
Chris Saxon
May 11, 2018 - 1:05 pm UTC

Thanks for sharing Stew

More to Explore

Design

New to good database design? Check out Chris Saxon's full fundamentals class.