Re: view re-creation

From: Jared Still <jkstill_at_gmail.com>
Date: Thu, 16 Jul 2009 17:10:56 -0700
Message-ID: <bf46380907161710r44017526ja98ff7227c66398d_at_mail.gmail.com>

On Thu, Jul 16, 2009 at 4:37 PM, Ram Raman <veeeraman_at_gmail.com> wrote:

> Hi
>
> We are trying to create a few hunded views whose definitions are very big,
> longer than 2500 characters. We get the definitions from one database and
> create in a target database. Extracting the definition via dbms_metadata or
> selecting text from dba_views
> gives an output that is like this:
>
>

As Mark stated, use 'SET LONG'.

However, generating DDL for views is kind of problematic.

DBMS_METADATA is buggy, and the SQL generated for views is generally unusable.

The DBA_VIEWS.TEXT column contains the SELECT portion of the view, but in many
cases the column names will be incorrect.

The options I know of:

1. write some code to correctly generate the views. this is rather time consuming
2. create schema only exports, and grep the view statements from the file. Search on 'CREATE OR REPLACE VIEW' and 'CREATE OR REPLACE FORCE VIEW'

If there's an easier way to do this, I would also like to know.

Jared Still
Certifiable Oracle DBA and Part Time Perl Evangelist

--
http://www.freelists.org/webpage/oracle-l

Received on Thu Jul 16 2009 - 19:10:56 CDT