Home » SQL & PL/SQL » SQL & PL/SQL » Getting Table Hierarchy from a schema
Getting Table Hierarchy from a schema [message #399834] Fri, 24 April 2009 04:34 Go to next message
Neo06
Messages: 11
Registered: January 2008
Junior Member
Am trying to create table hierarchy like child tables first and then parent tables. Need this list to populate the tables with sample data. I searched the forums and on google and got the following script

create table master_child as
select k.ptable parent_table,ctable child_table
from (select distinct B.table_name ctable, 
                      a.table_name ptable,
                      b.constraint_name
      from user_constraints A, user_constraints B
      where B.r_constraint_name = A.constraint_name
      and B.r_owner = A.owner
      and B.constraint_type ='R') k, user_cons_columns l
where k.ctable=l.table_name
and k.ptable!=l.table_name
and k.constraint_name = l.constraint_name
order by 1;

select * from master_child order by child_table;

PARENT_TABLE                   CHILD_TABLE                    
------------------------------ ------------------------------ 
REGIONS                        COUNTRIES                      
EMPLOYEES                      DEPARTMENTS                    
LOCATIONS                      DEPARTMENTS                    
DEPARTMENTS                    EMPLOYEES                      
JOBS                           EMPLOYEES                      
DEPARTMENTS                    JOB_HISTORY                    
JOBS                           JOB_HISTORY                    
EMPLOYEES                      JOB_HISTORY                    
COUNTRIES                      LOCATIONS                      

9 rows selected


My purpose is to generate sample data to populate the all tables in a given schema. My script would be scanning the schema on which its being run
- dentify the order in which the table needs to be populated
- delete data if already present
- generate sample data and popualte the tables


In the above code snippet, there are more than one master tables for one child table.Am struck at this point, I hope someone can guide me how to go about further. I tried with connect by prior stuffs, but am poor at it, I get error "ORA-01436: CONNECT BY loop in user data"



Re: Getting Table hierarchy from a schema [message #399838 is a reply to message #399834] Fri, 24 April 2009 04:38 Go to previous messageGo to next message
Michel Cadot
Messages: 64151
Registered: March 2007
Location: Nanterre, France, http://...
Senior Member
Account Moderator
To prevent from this error you have to use NOCYCLE option.

Regards
Michel
Re: Getting Table hierarchy from a schema [message #399862 is a reply to message #399838] Fri, 24 April 2009 05:24 Go to previous messageGo to next message
Neo06
Messages: 11
Registered: January 2008
Junior Member
@Michel
As I said am poor at using Hierarchical Queries, I tired with the following code but got error as "ORA-00920: invalid relational operator"
select parent_table, child_table, level
from master_child
connect by nocycle prior  parent_table = child_tab


Also have doubt like, please refer my code snippet at thread start, all child tables doesn't come under parent table. Can I use Hierarchical queries for this.

I would need my result to be like this

REGIONS
COUNTRIES
LOCATIONS
DEPARTMENTS
EMPLOYEES
JOBS
JOB_HISTORY

since there are two masters for DEPARTMENTS like (EMPLOYEES, LOCATION) am confused on proceeding coz, to insert into emplyees table I need 'DeptID' but inserting in DEPARTMENTS table I need "MGR ID which I enter in EMPLOYEES table. Now which table should I enter first..? Is it like I need to disable all constraints before I do anything..?. But getting table order is also curcial for me to generate sample data.




Re: Getting Table hierarchy from a schema [message #399874 is a reply to message #399862] Fri, 24 April 2009 05:57 Go to previous messageGo to next message
Michel Cadot
Messages: 64151
Registered: March 2007
Location: Nanterre, France, http://...
Senior Member
Account Moderator
Maybe you have an inappropriate version but you didn't follow the guidelines and didn't post it.

Regards
Michel
Re: Getting Table Hierarchy from a schema [message #399875 is a reply to message #399834] Fri, 24 April 2009 06:02 Go to previous messageGo to next message
Neo06
Messages: 11
Registered: January 2008
Junior Member
Oops sorry,I gave the version while posting new thread. so thought it would be visible.

Am using Oracle 9i on windowsXP.

Re: Getting Table Hierarchy from a schema [message #399882 is a reply to message #399875] Fri, 24 April 2009 06:26 Go to previous messageGo to next message
Michel Cadot
Messages: 64151
Registered: March 2007
Location: Nanterre, France, http://...
Senior Member
Account Moderator
And NOCYCLE was introduced in 10.1.

Regards
Michel
Re: Getting Table Hierarchy from a schema [message #402396 is a reply to message #399882] Sat, 09 May 2009 16:55 Go to previous message
Kevin Meade
Messages: 2101
Registered: December 1999
Location: Connecticut USA
Senior Member
It is a messy problem. You will have to write your own. The key is to keep an array in plsql of the names of tables you have seen. When you see a table you have seen before, don't go up/down that foreign key chain again. Eventually you will have a hierarchical list of tables in your array.

It may take a day for you to write it.

Good luck, Kevin
Previous Topic: unable to reduce the IO Cost of the query
Next Topic: Getting a letter after a space
Goto Forum:
  


Current Time: Fri Dec 09 13:56:22 CST 2016

Total time taken to generate the page: 0.15617 seconds