Home » SQL & PL/SQL » SQL & PL/SQL » Dynamic view creation
icon2.gif  Dynamic view creation [message #349571] Mon, 22 September 2008 04:31 Go to next message
somen
Messages: 9
Registered: May 2007
Location: KOLKATA
Junior Member
Hi All,
I've an urgent requirement to write a procedure which will accept a table name as a parameter and will create view based on a rule.
The rule is as follows:
First, I've to select the tables which have primary/foreign key relationship with the very table(passed as a parameter). Let me represent these as Set1.
Now I've to select the tables those have primary/foreign key relationship with the tables in Set1. Let me represent these as Set2.
Tables in set2 must not be in set1.
Now,I have to create view on those tables in set1 and set2(filtered).

For example, Let A be the (detail)table to be passed as parameter to that procedure. Let A1,A2,A3 are the tables found in Set1.
Now for A1(detail at this stage), these are tables found: B, A2, C, D, A3 which are in Set2.
Now I've to create view based on A1,C,D. (A2 and A3 will be discarded since they belong to Set1).
The same thing will be for other tables in set1(i.e. for A2, A3 and so on).

Any suggestion is accepted.

Regards,
Somen.
(somen.choudhury@rediffmail.com).

[Updated on: Mon, 22 September 2008 04:39] by Moderator

Report message to a moderator

Re: Dynamic view creation [message #349576 is a reply to message #349571] Mon, 22 September 2008 04:40 Go to previous messageGo to next message
Michel Cadot
Messages: 64139
Registered: March 2007
Location: Nanterre, France, http://...
Senior Member
Account Moderator
First read OraFAQ Forum Guide.
Then post the requested information including what you already tried.

Regards
Michel
Re: Dynamic view creation [message #349578 is a reply to message #349571] Mon, 22 September 2008 04:54 Go to previous message
JRowbottom
Messages: 5933
Registered: June 2006
Location: Sunny North Yorkshire, ho...
Senior Member
you're going to need USER/ALL_CONSTRAINTS to get the table linkages, and USER/ALL_CONS_COLUMNS to get the details fo the join conitions to actually build the SQL with.

A very imnportant question: Why do you think you need this, and particularly, why do you think you need it to be dynamic?

What problem are you trying to solve?
Previous Topic: Change Number to Time format
Next Topic: Simple Query
Goto Forum:
  


Current Time: Fri Dec 09 00:02:09 CST 2016

Total time taken to generate the page: 0.22489 seconds