| help in simple plsql [message #39303] |
Thu, 04 July 2002 08:28  |
ksr
Messages: 112
Registered: January 2002
|
Senior Member |
|
|
Hi,
create table test_relations (maintable varchar2(100),relationtable varchar2(100));
I want a plsql code which does the functionality..
what i want exactly is, the first cursor will get me the tablenames starting with TFIX.
Now i want to get the tables which has a relation(child tables) for each of this table and store them in a table(both the TFIX table and the child tables for that)..
1) select tname from tab where tname like 'TFIX%';
2) get the child tables for each of the TFIX table
SELECT TABLE_NAME FROM DBA_CONSTRAINTS WHERE R_CONSTRAINT_NAME IN
(SELECT CONSTRAINT_NAME FROM dba_constraints WHERE TABLE_NAME = cur1.tname(table which was fetched from the first query )
3) Insert them into another table both with master and child tables
(its ok for me if the final result look like
Master1 child1
Master1 child2
Master1 child3 ..
Master2 Child1
Master2 Child1
Sometimes i get error when there are no child tables present for the master.
How to do it...
|
|
|
|
| Re: help in simple plsql [message #39309 is a reply to message #39303] |
Fri, 05 July 2002 00:03  |
Mahesh
Messages: 90
Registered: January 2001
|
Member |
|
|
cursor c1 select tname from tab where tname like 'TFIX%';
cursor c2(c1.tname) as SELECT TABLE_NAME FROM DBA_CONSTRAINTS WHERE R_CONSTRAINT_NAME IN (SELECT CONSTRAINT_NAME FROM dba_constraints WHERE TABLE_NAME = cur1.tname
with these two declarations, try solving the problem.
use two for loops
the second loop for insert the new table created.
hope this helps .. let me know if i can help u better
|
|
|
|
] 
Blog
Search
Help
Members
Register
Login
Home
