How to format DBMS_METADATA.GET_DDL?

To get a formatted definition of your DDLs, with all the semicolons and avoid lines break (Wrapped lines).
Here is an example to retrieve the DDL of a triggers using the DBMS_METADATA package.

For Oracle 18c / 12c / 11g / 10g:

COLUMN Text FORMAT a2480 WORD_WRAPPED

SET LONG 20000 LONGCHUNKSIZE 20000 PAGESIZE 0 LINESIZE 1000 FEEDBACK OFF VERIFY OFF TRIMSPOOL ON

REM Without use of spool, remove "REM"
REM set term on

REM With use of spool (no display on stdout)
set term off


BEGIN
   DBMS_METADATA.set_transform_param (DBMS_METADATA.session_transform, 'SQLTERMINATOR', true);
   DBMS_METADATA.set_transform_param (DBMS_METADATA.session_transform, 'PRETTY', true);
END;
/


spool ddl_schema_trigger.sql
SELECT DBMS_METADATA.GET_DDL('TRIGGER', 'trigger_name', 'schema_name') Text FROM SYS.DUAL;
spool off

set feed on echo on head on pages 14 term on

REM *****************************************
REM New script ======> ddl_schema_trigger.sql
REM *****************************************

Other useful options you can use:

exec DBMS_METADATA.SET_TRANSFORM_PARAM (DBMS_METADATA.SESSION_TRANSFORM, ‘STORAGE’,false);
exec DBMS_METADATA.SET_TRANSFORM_PARAM (DBMS_METADATA.SESSION_TRANSFORM, ‘SEGMENT_ATTRIBUTES’,false);

The generate all the DDL for a schema it’s here.

See also:
Sometimes we need to create a user similar to another user or we need to recreate a user in another database.In those situations,Oracle supplied package.function dbms_metadata.get_ddl user can be used to generate the user creation DDL.

 

Author: Vincent Fenoll

Compatibility: Oracle 18c, 12g, 11g