RE: view re-creation

Try this or combination therof   p.s. longchunk is one of the secrets.
Then execute your get_ddl

 

SET LONG 1000000

SET LONGCHUNK 1000000

 

EXECUTE
DBMS_METADATA.SET_TRANSFORM_PARAM(DBMS_METADATA.SESSION_TRANSFORM,'STORA
GE',false);

EXECUTE
DBMS_METADATA.SET_TRANSFORM_PARAM(DBMS_METADATA.SESSION_TRANSFORM,'PRETT
Y',true);

EXECUTE
DBMS_METADATA.SET_TRANSFORM_PARAM(DBMS_METADATA.SESSION_TRANSFORM,'SQLTE
RMINATOR',true);

EXECUTE
DBMS_METADATA.SET_TRANSFORM_PARAM(DBMS_METADATA.SESSION_TRANSFORM,'REF_C
ONSTRAINTS',false);

 

 

begin

 
dbms_metadata.set_transform_param(dbms_metadata.session_transform,'PRETT
Y',TRUE);

 
dbms_metadata.set_transform_param(dbms_metadata.session_transform,'SQLTE
RMINATOR',TRUE);

 
dbms_metadata.set_transform_param(dbms_metadata.session_transform,'SEGME
NT_ATTRIBUTES',TRUE);

 
dbms_metadata.set_transform_param(dbms_metadata.session_transform,'STORA
GE', TRUE);

 
dbms_metadata.set_transform_param(dbms_metadata.session_transform,'TABLE
SPACE',TRUE);

 
dbms_metadata.set_transform_param(dbms_metadata.session_transform,'SPECI
FICATION',TRUE);

 
dbms_metadata.set_transform_param(dbms_metadata.session_transform,'BODY'
,TRUE);

 
dbms_metadata.set_transform_param(dbms_metadata.session_transform,'CONST
RAINTS',TRUE);

end;

/

 

 

Joel Patterson 
Database Administrator 
904 727-2546 

________________________________

From: oracle-l-bounce@xxxxxxxxxxxxx
[mailto:oracle-l-bounce@xxxxxxxxxxxxx] On Behalf Of Ram Raman
Sent: Thursday, July 16, 2009 7:38 PM
To: ORACLE-L
Subject: view re-creation

 

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:

 

 

SELECT 999 , C.SETID , A.BUSINESS_UNIT , A.DEMAND_SOURCE ,
A.SOURCE_BUS_UNIT , A
.ORDER_NO , A.ORDER_INT_LINE_NO , A.SCHED_LINE_NO , A.INV_ITEM_ID ,
A.DEMAND_LIN
E_NO , A.CUST_ID , A.SCHED_DATE , A.SCHED_TIME , A.QTY_REQUESTED ,
MAX(A.QTY_REQ
UESTED - A.QTY_BACKORDER) , MAX(A.QTY_REQUESTED_BASE -
A.QTY_BACKORDER_BASE) , A
.QTY_BACKORDER_BASE , A.QTY_BACKORDER , A.UNIT_OF_MEASURE ,
A.PRIORITY_NBR , A.S
HIP_TO_CUST_ID , A.SHIP_CUST_NAME1 , A.LOCATION , A.PARTIAL_QTY_FLAG ,
A.BCKORDR
.....

 

 

The lines are cut at the end. The resulting DDL needs lots of editing
and it is very time consuming editing the file  which has 140,000 lines.
Is there a way to extract the view definitions without this problem. I
tried adjusting the linesize in sqlplus, but it did not help. Tested in
both windows sqlplus and Unix env. 

 

Thanks.

Other related posts: