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”