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

"Alistair" <alistair.thomson_at_sphinxcst.co.uk> wrote in message news:<a2m00j$sjq$1_at_thorium.cix.co.uk>...
> Why not take an export and use that to create the views? If its just the
> views you're interested in you could take an export with rows=no (very
> quick) and then either run the import to build the views or edit the syntax
> out of the export.
>
> Alistair
>
> "Richard Shea" <richardshea_at_fastmail.fm> wrote in message
> news:282f826a.0201221517.44ac31c4_at_posting.google.com...
> > Hi - I want a script which will recreate all views in a schema. I've
> > written :
> >
> > set long 4000
> > set heading off
> > set line 4000
> > select 'create or replace view ' || view_name, ' as ', text from
> > user_views
> >
> > which seems to be halfway there but now I need (I think) to put a '/'
> > at then end of each command. Can anyone tell me how to do that ?
> >
> > While I'm asking I think having to set the line/long length to a given
> > width in order to not truncate is a bit clunky too is there a better
> > way to deal with the 'text' column (which has a type of 'long') ?
> >
> > thanks
> >
> > Richard Shea.
> >
> >
> > PS: Thinking about alternatives to this approach I'm on a W2K box so I
> > can't easily use the various (unix based) scripts to unmangle an EXP
> > output and although I've got a copy of TOAD (albeit the free version)
> > I can't see a way to select all views at one go in order to have them
> > all scripted - maybe I've missed that ?

Alistair, try adding

set trimspool on -- to the start of your script to eliminate trailing blanks from your lines

select ';' from sys.dual; -- right after the query to extract the view code

You will also need to account for the view column names which may differ from the defining select list column names. You can pull these from dba_tab_columns.
I found it easier to use pl/sql to pull the column names and just use SQL before and after the pl/sql to create everything else.

HTH

• Mark D Powell --