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#';

Comments

Popular posts from this blog

Highlight the cell of Interactive Report based on Search Criteria in Oracle APEX

Querying Data From Flat Files in Oracle

Number of Tables by The Number of Rows in Oracle Database

Save Selected Interactive Grid Records into a Collection - Oracle APEX

Highlight Data That Meet Certain Criteria in Oracle APEX Report

Return a Validation Message using JavaScript if the Oracle Application Express Item is Empty

Remove X (Close Mark) from Oracle APEX Modal Dialog

Display User Rating Icons Dynamically in Oracle APEX Report

Printing Page Numbers in RTF Template [Oracle BI Publisher]

Find Where Specific Table or View is Used in Oracle Database