Link to home
Start Free TrialLog in
Avatar of Mamarazzi
MamarazziFlag for Sweden

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?

ASKER CERTIFIED SOLUTION
Avatar of konektor
konektor
Flag of Czechia image

Link to home
membership
This solution is only available to members.
To access this solution, you must be a member of Experts Exchange.
Start Free Trial
Avatar of Mamarazzi

ASKER

My God! Some of you guys are quick! And the question was a real RTFM!!! I feel ashamed... :-)

Thank you!