oracle get_ddl
-- DDL object_type
SELECT OWNER,
OBJECT_TYPE,
decode(object_type,
'DATABASE LINK', 'DB_LINK',
'JOB', 'PROCOBJ',
'RULE SET', 'PROCOBJ',
'RULE', 'PROCOBJ',
'EVALUATION CONTEXT', 'PROCOBJ',
'CREDENTIAL', 'PROCOBJ',
'CHAIN', 'PROCOBJ',
'PROGRAM', 'PROCOBJ',
'PACKAGE', 'PACKAGE_SPEC',
'PACKAGE BODY', 'PACKAGE_BODY',
'TYPE', 'TYPE_SPEC',
'TYPE BODY', 'TYPE_BODY',
'MATERIALIZED VIEW', 'MATERIALIZED_VIEW',
'QUEUE', 'AQ_QUEUE',
'JAVA CLASS', 'JAVA_CLASS',
'JAVA TYPE', 'JAVA_TYPE',
'JAVA SOURCE', 'JAVA_SOURCE',
'JAVA RESOURCE', 'JAVA_RESOURCE',
'XML SCHEMA', 'XMLSCHEMA',
object_type
) AS object_type_ddl
FROM DBA_OBJECTS;
-- DDL of object depending on object_type_ddl
SELECT dbms_metadata.get_ddl('object_type_ddl', 'obj_name', 'schema_name')
FROM DUAL;