Generating DDL Script

Oracle has a built in package called DBMS_METADATA  to generates the Oracle Data Definition Language(DDL) for database objects like View, Table, Synonym, Index etc. This package is very handy to extract DDL for any given Oracle objects. You do not need to dig through the stacks of Oracle Views, SQL Developer broweser or other IDEs to get the DDL script. Using this package saves you some time.

Why do you need DDL Script?

I use this package to extract the DDL for any objects before modifying it or to push object to another Oracle Instance. Keeping an original copy of DDL script before modifying helps you to rollback the changes in the event things don't go as planned. A DDL script can be generated to any any objects that exist on dba_objects View. Below I will show you some examples to generate DDL using Oracle package.

View DDL:

SELECT dbms_metadata.Get_ddl (object_type, object_name, owner) 
FROM   dba_objects 
WHERE  object_type = 'VIEW' 
       AND owner = 'BIN_ADMIN' 
       AND object_name = 'EMP_VIEW';  

Table DDL:

SELECT dbms_metadata.Get_ddl (object_type, object_name, owner) 
FROM   dba_objects 
WHERE  object_type = 'TABLE' 
       AND owner = 'BIN_ADMIN' 
       AND object_name = 'STUDENT';  

 Index DDL

SELECT dbms_metadata.Get_ddl (object_type, object_name, owner) 
FROM   dba_objects 
WHERE  object_type = 'INDEX' 
       AND owner = 'BIN_ADMIN' 
       AND object_name = 'STUDENT_IDX'; 

By now, you must have clear idea on how this package works. You just have to supply the objects types, name and the owner. DBA_OBJECTS view should be able to supply you with the necessary parameters required for the package.