RE: dbms_metadata.get_ddl

From: Adams, Matthew (GE, Appl & Light) <"Adams,>
Date: Mon, 30 Sep 2013 14:35:59 +0000
Message-ID: <D4EBD31A58EB64419330A4F471BB2F24159ACBB8_at_CINURCNA02.e2k.ad.ge.com>

I use dbms_metadata regularly to reverse generate the DDL for pretty much my entire database.

My general structure for the generation scripts is:

set pagesize 0
set long 10000
set trimspool on
set linesize 1000
set feedback off
set longchunksize 10000

exec dbms_metadata.set_transform_param(dbms_metadata.session_transform, 'SQLTERMINATOR', true);

spool output.txt
<Many calls to dbms_metadata.get_ddl>

Spool off

For myself at least, this generates DDL that executes with no problems.

Matt "It's time to brew beer again" Adams

-----Original Message-----

From: oracle-l-bounce_at_freelists.org [mailto:oracle-l-bounce_at_freelists.org] On Behalf Of Ray Stell Sent: Monday, September 30, 2013 10:15 AM To: oracle-l_at_freelists.org
Subject: dbms_metadata.get_ddl

Whenever I try to use the dbms_metadata.get_ddl the output is not valid. I think the issue must be terminal related. The docs say use these:

SQL> SET PAGESIZE 0
SQL> SET LONG 1000000
SQL> SELECT get_table_md FROM dual;

and then shows perfectly formated output. I wonder what editor they used to repair the damage? When I use it there are linebreaks in the middle of strings that produce errors.

This thread https://forums.oracle.com/thread/703189 reports using:

set pagesize 0
set long 90000
set lines 131
column txt format a121 word_wrapped
set longchunksize to 250

This seemed to work a little better, but I feel like it won't on the next object. Tom has this rambling thread: http://asktom.oracle.com/pls/asktom/f?p=100:11:0::::P11_QUESTION_ID:30802454515375

Seems like rather a fishing expedition. Is there some magic that just works?

TIA
--

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

--

http://www.freelists.org/webpage/oracle-l Received on Mon Sep 30 2013 - 16:35:59 CEST