set serveroutput on size 50000
set linesize 400
undefine owner
set verify off
set heading off
set feedback off
set longchunksize 9000
set long 9000
spool create_views.sql
declare
cursor c1(p_owner varchar2) is
  select view_name, text, text_length
  from   dba_views
  where  owner = p_owner
  order by view_name;
cursor c2(p_owner varchar2, p_view varchar2) is
  select column_name, column_id
  from   dba_tab_columns
  where  table_name = p_view
  and    owner = p_owner
  order by column_id;
begin
dbms_output.put_line('-- MUST COPY VIEW TEXT INTO VIEW DEFINITION');  
dbms_output.put_line('-- ****************************************');  
for c1_rec in c1('&&owner') loop
  dbms_output.put_line('create view '||c1_rec.view_name);
  for c2_rec in c2('&&owner',c1_rec.view_name) loop
    if c2_rec.column_id = 1 then
      dbms_output.put_line('("'||c2_rec.column_name||'"');
    else
      dbms_output.put_line(',"'||c2_rec.column_name||'"');
    end if;
  end loop;
dbms_output.put_line(') AS');  
dbms_output.put_line('INSERT TEXT');  
end loop;
end;
/
select view_name, text,'/' from dba_views
where owner = '&&owner'
order by view_name;
spool off
