RE: dbms_metadata.get_ddl

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

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: [] On Behalf Of Ray Stell Sent: Monday, September 30, 2013 10:15 AM To:
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 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 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:

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


-- Received on Mon Sep 30 2013 - 16:35:59 CEST

Original text of this message