Answers for "GET DETAIL OF DDLS ORACLE"

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;

oracle get ddl

-- 4000 characters max
SELECT dbms_metadata.get_ddl('PROCOBJ', 'job_name', 'owner') FROM DUAL;
SELECT dbms_metadata.get_ddl('PROCOBJ', 'program_name', 'owner') FROM DUAL;
SELECT dbms_metadata.get_ddl('TABLE', 'table_name', 'owner') FROM DUAL;
SELECT dbms_metadata.get_ddl('VIEW', 'view_name', 'owner') FROM DUAL;

SELECT dbms_metadata.get_ddl('PACKAGE', 'pkg_name', 'owner') FROM DUAL; 
SELECT dbms_metadata.get_ddl('PROCEDURE', 'proc_name', 'owner') FROM DUAL; 

SELECT dbms_metadata.get_ddl('INDEX', 'index_name', 'owner') FROM DUAL;
SELECT dbms_metadata.get_ddl('TYPE', 'type_name', 'owner') FROM DUAL;