SQL Script Generate Object DDL

Updated: 24-Nov-2004
Version: 1.0

Description

This script uses the DBMS_METADATA package tp generate the DDL of the majority of objects in the database.

Parameters

Parameters change depending upon the object type selected.

SQL Source

REM Copyright (C) Think Forward.com 1998- 2005. All rights reserved. 

set long 50000
set pages 2000
set heading off feed off
set verify off
set serveroutput on size 1000000

spool genobj.lst

PROMPT
PROMPT Valid Object types Supported : 
PROMPT                                 CLUSTER
PROMPT                                 CONSTRAINT
PROMPT                                 CONTEXT
PROMPT                                 DATABASE LINK
PROMPT                                 DIMENSION
PROMPT                                 DIRECTORY
PROMPT                                 FUNCTION
PROMPT                                 INDEX
PROMPT                                 INDEXTYPE
PROMPT                                 JAVA SOURCE
PROMPT                                 LIBRARY
PROMPT                                 MATERIALIZED VIEW
PROMPT                                 OPERATOR
PROMPT                                 PACKAGE
PROMPT                                 PACKAGE BODY
PROMPT                                 PROCEDURE
PROMPT                                 ROLE
PROMPT                                 SEQUENCE
PROMPT                                 SYNONYM
PROMPT                                 TABLE
PROMPT                                 TRIGGER
PROMPT                                 TYPE
PROMPT                                 TYPE BODY                             
PROMPT                                 USER
PROMPT                                 VIEW
PROMPT                                 XML SCHEMA

PROMPT
ACCEPT obj_type PROMPT 'Enter Value for Object Type :'
PROMPT
PROMPT Object Name Context
PROMPT
PROMPT CONSTRAINT    (object_name => all PK/FK constraints for table_name)
PROMPT INDEX         (object_name => all indexes for table_name)
PROMPT
ACCEPT owner PROMPT 'Enter Value For Owner  for all : '
ACCEPT obj_name PROMPT 'Enter Value for Object Name  for all :'

WITH genobject AS (
   select object_type
          , dbms_metadata.get_ddl(
              DECODE(object_type,'DATABASE LINK','DB_LINK'
                                 ,'JAVA SOURCE','JAVA_SOURCE'
                                 ,'MATERIALIZED VIEW','MATERIALIZED_VIEW'
                                 ,'PACKAGE BODY','PACKAGE_BODY'
                                 ,'TYPE BODY','TYPE_BODY'
                                 ,'XML SCHEMA','XMLSCHEMA'
                    ,object_type)
              , object_name
              , DECODE(object_type,'DIRECTORY', null
                    ,owner)
              ) ddl
   from dba_objects
   where owner like UPPER('%&owner%')
   and object_name like UPPER('%&obj_name%')
   and object_type = UPPER('&obj_type')
   ),
     genindx AS (
   select 'INDEX' object_type
          , dbms_metadata.get_ddl('INDEX', index_name, owner ) ddl
   from dba_indexes
   where owner like UPPER('%&owner%')
   and table_name like UPPER('%&obj_name%')
   ),
     gencons AS (
   select 'CONSTRAINT' object_type
          , dbms_metadata.get_ddl('CONSTRAINT', constraint_name, owner ) ddl
   from dba_constraints
   where owner like UPPER('%&owner%')
   and table_name like UPPER('%&obj_name%')
   and constraint_type in ('P','R')
   ),
     genrole AS (
   select 'ROLE' object_type
          , dbms_metadata.get_ddl('ROLE', role) ddl
   from dba_roles
   where role like UPPER('%&obj_name%')
   ),
     genuser AS (
   select 'USER' object_type
          , dbms_metadata.get_ddl('USER', username) ddl
   from dba_users 
   where username like UPPER('%&obj_name%')
   )
SELECT dbms_lob.substr(ddl)
FROM genobject WHERE object_type = UPPER('&obj_type')
UNION
SELECT dbms_lob.substr(ddl)
FROM genindx WHERE object_type = UPPER('&obj_type')
UNION
SELECT dbms_lob.substr(ddl)
FROM gencons WHERE object_type = UPPER('&obj_type')
UNION
SELECT dbms_lob.substr(ddl)
FROM genrole WHERE object_type = UPPER('&obj_type')
UNION
SELECT dbms_lob.substr(ddl)
FROM genuser WHERE object_type = UPPER('&obj_type')
/

spool off

Return to Index of SQL Scripts

Site Map

General Information : Home | Company Profile | Contact Us | Legal
Database Support : Proactive Support | Pay Per Incident Support
Expert Services : High Availability | Creative Solutions | Training
Build Services : Oracle Installations | Revive your Oracle Setup | Low Cost Development
Free Oracle Resources: DBA Scripts | Oracle Links