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.
