Re: script to recreate all views - problem with 'text' column in user

Home -> Community -> Usenet -> c.d.o.misc -> Re: script to recreate all views - problem with 'text' column in user_views

From: Knut Talman <knut.talman_at_mytoys.de>
Date: Wed, 23 Jan 2002 14:50:38 +0100
Message-ID: <3C4EBFAE.F5583E53@mytoys.de>

> select 'create or replace view ' || view_name, ' as ', text from
> user_views

What about a little PL/SQL like

declare
  CURSOR cViews IS
    SELECT view_name, text FROM user_views;   v_outfl UTL_FILE.FILE_TYPE;
begin
  v_outfl := UTL_FILE.FOPEN('/tmp', 'createviews.sql','w');   FOR rViews IN cViews LOOP
    UTL_FILE.PUTF(v_outfl,'create or replace view %s as %s;\n',rViews.view_name,rViews.text);
  END LOOP;
  UTL_FILE.FCLOSE(v_outfl);
end;
/

Just take care that the directory where you put the file is listed in UTL_FILE_DIR (in init.ora).

Regards,

Knut Received on Wed Jan 23 2002 - 07:50:38 CST