Creating Object DDL

Here is some handy code to create object DDL using DBMS_METADATA.

There are 2 scripts, I shall call them the calling script and the running script.

The calling script (which I called get_obj.sql) is as follows

doc
 Possible objects to get DDL for are :-
  CONSTRAINT
  DB_LINK
  DIRECTORY
  FUNCTION
  INDEX
  MATERIALIZED_VIEW
  MATERIALIZED_VIEW_LOG
  PACKAGE_SPEC
  PACKAGE_BODY
  PROCEDURE
  SEQUENCE
  SYNONYM
  TABLE
  TRIGGER
  TYPE_SPEC
  TYPE_BODY
  VIEW
#

accept Object prompt  'Enter Object type -> '
accept OwnerIN prompt   "Enter Schema Owner (default %) -> "
accept ObjNameIN prompt "Enter Object Name (default %) -> "

set verify off 
set head off
set feed off
set pages 0
set trims on
set term off
set lines 200

col spool_on      for a200
col select_clause for a200
col from_clause   for a200
col where_clause  for a200
col and_clause    for a200
col object_clause for a200
col spool_off     for a200

col object_type_dir  new_value dir_and_type  noprint
col object_type_dirs new_value dir_and_types noprint

col OwnerOUT new_value Owner noprint
col ObjNameOUT new_value ObjName noprint

select nvl('&OwnerIN','%') OwnerOUT , nvl('&ObjNameIN','%') ObjNameOUT from dual;

SELECT 
   CASE upper('&Object')
  WHEN 'MATERIALIZED_VIEW'     THEN 'mview'
  WHEN 'MATERIALIZED_VIEW_LOG' THEN 'mview_log'
  WHEN 'PACKAGE_SPEC'          THEN 'package'
  WHEN 'TYPE_SPEC'             THEN 'type'
  ELSE                              lower('&Object')
   END        object_type_dir 
FROM      dual;

SELECT 
  CASE '&dir_and_type'
  WHEN 'index'                 THEN 'indexes'
  WHEN 'package_body'          THEN 'package_bodies'
  WHEN 'type_body'             THEN 'type_bodies'
  ELSE                              '&dir_and_type'||'s'
  END         object_type_dirs
FROM     dual;

spool get_sp_obj.sql

SELECT     'spool do_get_sp_obj.sql'                                                spool_on
 ,  q'[SELECT '@@get_ddl '||upper('&Object')||' '||]'
 || CASE '&Object'
  WHEN 'MATERIALIZED_VIEW_LOG' THEN q'[log_owner||' '||log_table||' '||log_owner||'/]'||'&dir_and_types'||q'[ '||master]'
  WHEN 'DB_LINK'               THEN q'[owner||' '||db_link||' '||owner||'/]'||'&dir_and_types'||q'[/'||db_link]'
  WHEN 'FUNCTION'              THEN q'[owner||' '||object_name||' '||owner||'/]'||'&dir_and_types'||q'[ '||object_name]'
  WHEN 'PROCEDURE'             THEN q'[owner||' '||object_name||' '||owner||'/]'||'&dir_and_types'||q'[ '||object_name]'
  WHEN 'PACKAGE_SPEC'          THEN q'[owner||' '||object_name||' '||owner||'/]'||'&dir_and_types'||q'[ '||object_name]'
  WHEN 'PACKAGE_BODY'          THEN q'[owner||' '||object_name||' '||owner||'/]'||'&dir_and_types'||q'[ '||object_name]'
  WHEN 'SEQUENCE'              THEN q'[sequence_owner||' '||sequence_name||' '||sequence_owner||'/]'||'&dir_and_types'||q'[ '||sequence_name]'
  WHEN 'TYPE_SPEC'             THEN q'[owner||' '||type_name||' '||owner||'/]'||'&dir_and_types'||q'[ '||type_name]'
  WHEN 'TYPE_BODY'             THEN q'[owner||' '||object_name||' '||owner||'/]'||'&dir_and_types'||q'[ '||object_name]'
  ELSE                              q'[owner||' '||]'||'&dir_and_type'||q'[_name||' '||owner||'/]'||'&dir_and_types'||q'[ '||]'||'&dir_and_type'||'_name'
    END                                                                      select_clause
 ,  'from '
 || CASE '&Object'
  WHEN 'FUNCTION'              THEN 'dba_objects'
  WHEN 'PROCEDURE'             THEN 'dba_objects'
  WHEN 'PACKAGE_SPEC'          THEN 'dba_objects'
  WHEN 'PACKAGE_BODY'          THEN 'dba_objects'
  WHEN 'TYPE_SPEC'             THEN 'dba_types'
  WHEN 'TYPE_BODY'             THEN 'dba_objects'
  ELSE                              'dba_'||'&dir_and_types'
    END                                                                      from_clause
 , 'where '
 || CASE '&Object'
  WHEN 'MATERIALIZED_VIEW_LOG' THEN 'log_owner'
  WHEN 'SEQUENCE'              THEN 'sequence_owner'
  ELSE                              'owner'
    END
 || q'[ like upper('&Owner')]'                                                    where_clause
 , 'and '
 || CASE '&Object'
  WHEN 'FUNCTION'              THEN q'[object_type = 'FUNCTION']'
  WHEN 'PROCEDURE'             THEN q'[object_type = 'PROCEDURE']'
  WHEN 'PACKAGE_SPEC'          THEN q'[object_type = 'PACKAGE']'
  WHEN 'PACKAGE_BODY'          THEN q'[object_type = 'PACKAGE BODY']'
  WHEN 'TYPE_BODY'             THEN q'[object_type = 'TYPE BODY']'
  ELSE                              '1 = 1'
    END                                                                     and_clause
 , 'and '
 || CASE '&Object'
  WHEN 'MATERIALIZED_VIEW_LOG' THEN q'[master like upper('&ObjName')]'
  WHEN 'DB_LINK'               THEN q'[db_link like upper('&ObjName')]'
  WHEN 'FUNCTION'              THEN q'[object_name like upper('&ObjName')]'
  WHEN 'PROCEDURE'             THEN q'[object_name like upper('&ObjName')]'
  WHEN 'PACKAGE_SPEC'          THEN q'[object_name like upper('&ObjName')]'
  WHEN 'PACKAGE_BODY'          THEN q'[object_name like upper('&ObjName')]'
  WHEN 'TYPE_SPEC'             THEN q'[type_name like upper('&ObjName')]'
  WHEN 'TYPE_BODY'             THEN q'[object_name like upper('&ObjName')]'
  ELSE                              '&dir_and_type'||q'[_name like upper('&ObjName')]'
    END
 || ';'                                                                     object_clause
 ,  'spool off'                                                             spool_off
FROM    dual
/
spool off

@@get_sp_obj.sql

@@do_get_sp_obj.sql

set verify on
set head on
set pages 60
set lines 140
set term on
set feed 5

This script as you can see on line 72 calls the running script called get_ddl.sql

Here is get_ddl.sql

set lines 32767
set pages 0
set head off
set feed off
set trims on
set verify off
set term off
set long 5000000

col text for a32767

!if [ ! -d &4 ]; then mkdir -p &4;fi

spool &4/&5..sql

select dbms_metadata.get_ddl('&1','&3','&2') text from dual
/

prompt /

spool off

Load both of these scripts into the same directory and call the calling script from SQL*Plus.

For example

@get_obj

You will be prompted to input some object_types and possibly names and each object creation script will be spooled to its individual file in a directory structure that matches the object type.
For example all the table definitions will be in a directory named 'tables'.

These scripts can be handy if you need to do a lot of reconfiguring or simply wish to have a record of schema objects.