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