Generate DDL source code using Oracle SQL

Here I am giving an example to generate DDL (Data Definition Language) source code using Oracle SQL.

One of my client wants to generate all the DDL source code for some database objects. So i just found the little piece of code to create DDL source.

Database Objects are,  

Ø  Sequence

Ø  Table

Ø  Index

Ø  View

Ø  Database Link

Ø  Materialized view

Ø  Function

Ø  Procedure

Ø  Package

Ø  Package Body

Oracle has provided default package dbms_metadata to generate DDL source code, we can make use of it.

SQL Query:

SELECT object_type,

              dbms_metadata.get_ddl (REPLACE(object_type,' ','_'), object_name, owner) AS ddl_source

    FROM all_objects

 WHERE object_type IN ('SEQUENCE', 'TABLE', 'INDEX', 'VIEW', 'PACKAGE',

                                         'PACKAGE BODY', 'FUNCTION', 'PROCEDURE', 'MATERIALIZED VIEW',

                                         'DATABASE LINK')

       AND owner          = '#SCHEMA_OWNER#';