DDL Generation–Oracle’s Answer to Save You Time and Money

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.

James Koopmann
James Koopmann
James Koopmann has fourteen years of database design, development and performance tuning experience. In addition, he has extensive database administration experience in Oracle and other relational databases in production environments, specializing in performance tuning of database engines and SQL based applications. Koopmann is an accomplished author with several technical papers in various Oracle related publications such as Oracle Magazine, Oracle Professional and SQL>UPDATE_RMOUG. He is a featured author and database expert for DatabaseJournal, a member of the editorial review committee for Select Journal (The Magazine for the International Oracle Users Group), an Oracle Certified Professional DBA and noted speaker at local Oracle User Groups around the country.

Latest Articles