Mamarazzi
asked on
Creating views by selecting from dba_views - determining the order
I want to create views in a schema, based on views in another schema.
This is the code:
(USER2 already has the tables in it's schema)
************************** ********** ********** ********** ********** ********** ********** ********** ********** ****
cursor c_views(cp_username in varchar2) is
select view_name, text
from dba_views
where owner = cp_username;
begin
for view_row in c_views ('USER1') loop
create_string := 'create view USER2.||view_row.view_name ||' as '||view_row.text||' ';
execute immediate create_string;
end loop;
end;
************************** ********** ********** ********** ********** ********** ********** *****'
All this works fine, but my problem is that some of the views are based on other views that haven't been created yet, so I get an error "Table or View doesn't exist". Therefore I need to create the views in the right order.
Any ideas on how to do that?
This is the code:
(USER2 already has the tables in it's schema)
**************************
cursor c_views(cp_username in varchar2) is
select view_name, text
from dba_views
where owner = cp_username;
begin
for view_row in c_views ('USER1') loop
create_string := 'create view USER2.||view_row.view_name
execute immediate create_string;
end loop;
end;
**************************
All this works fine, but my problem is that some of the views are based on other views that haven't been created yet, so I get an error "Table or View doesn't exist". Therefore I need to create the views in the right order.
Any ideas on how to do that?
ASKER CERTIFIED SOLUTION
membership
This solution is only available to members.
To access this solution, you must be a member of Experts Exchange.
ASKER
Thank you!