DBMS_METADATA.GET_DDL in sqlplus

Some settings matter when using dbms_metadata.

define large clobs

set long 1000000

large long columns

set longchunksize 32000

long lines

set linesize 32000

no trailing spaces

set trimspool on

no header

set heading off

no page size

set pages 0

no page feed (^L)

set newpage none

no start of page

set embedded on

no tabulator (^T)

set tab off

no feedback (n rows returned)

set feedback off

no echo

set echo off

Per default you get no terminator

exec DBMS_METADATA.SET_TRANSFORM_PARAM(DBMS_METADATA.SESSION_TRANSFORM,'SQLTERMINATOR',true)

When running the spooled code, allow blank lines

set sqlblanklines on

Get rid of &

set define off

Get rid of a leading #


set sqlprefix off

Get rid of . on a single line

set blockterminator OFF

To get rid of a trailing -, you may use set lin bigger than set longc and trimsp off, but I could not make it bug yet

CREATE TABLE T(x number default -
1);

would give 1 instead of -1 when run in sqlplus. But METADATA translates it to

CREATE TABLE "SCOTT"."T"
( "X" NUMBER DEFAULT -
1
)

So just forget about trailing dash for now.

Now try

SQL> set SQLBL ON SQLPRE OFF DEF OFF BLO OFF
SQL> create view v as select '
2
3 .
4 #?
5 &_date
6 ' x from dual;

View created.
SQL> set long 1000000 longc 32000 lin 32000 trims on hea off pages 0 newp none emb on tab off feed off echo off
SQL> exec DBMS_METADATA.SET_TRANSFORM_PARAM(DBMS_METADATA.SESSION_TRANSFORM,'SQLTERMINATOR',true)
SQL> select dbms_metadata.get_ddl('VIEW','V') from dual;

CREATE OR REPLACE FORCE VIEW "SCOTT"."V" ("X") AS
select '

.
#?
&_date
' x from dual;

Now you are safer to use dynamic sql in sqlplus. But hardly ever 100% safe.

Plenty of useful transformation parameters there to get rid of storage, tablespace and others.

1 thought on “DBMS_METADATA.GET_DDL in sqlplus

Comments are closed.