DDL Generation--Oracle's Answer to Save You Time and Money

May 1, 2003

Oracle has once again given us what we asked for, almost. A simple way to generate the DDL for objects defined in the database.

The Old Method

It used to be that in order to generate object DDL from the Oracle database, we had one of three methods we could use.

Build the SQL

Most of us used this method. We would have to first search through the available DBA_ views, piece together a wiz bang SQL statement, determine the proper syntax for the object we were reverse engineering, and pray that Oracle did not change syntax or put new features on our object in a future release.

Export Utility

The export utility was used with the show=y option and would just spit out the DDL. The problem with this was that we also had a bit of editing to do in order to put the fragmented pieces together. While this was a bit heavy on the editing, it did give us the satisfaction of knowing that the DDL was complete and we didn't need to worry about messing up syntax. Probably the greatest advantage to using the export utility was the ability to export a table's DDL along with all of its supporting DDL such as grants and constraints. Not only did you get all the DDL, you got a glimpse of the order to apply this DDL to not disrupt the natural order of applying constraints if you needed to rebuild the object.

Buy Third Party Software

If you had the money, you could purchase a product that knew everything about how Oracle pieced together DDL from the internal views. This was the easiest but if you did not have a budget, you would need to revert to building your own.