Database Journal
MS SQL Oracle DB2 Access MySQL PostgreSQL Sybase PHP SQL Etc SQL Scripts & Samples Tips Database Forum Rss Feed

» Database Journal Home
» Database Articles
» Database Tutorials
MS Access
SQL Scripts & Samples
» Database Forum
» Slideshows
Free Newsletters:

News Via RSS Feed

Rss Feed

Database Journal |DBA Support |SQLCourse |SQLCourse2

Featured Database Articles


Posted May 1, 2003

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

By James Koopmann

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.

Oracle Archives

Latest Forum Threads
Oracle Forum
Topic By Replies Updated
Oracle Data Mining: Classification jan.hasller 0 July 5th, 07:19 AM
Find duplicates - Unique IDs Lava 5 July 2nd, 08:30 AM
no matching unique or primary key rcanter 1 April 25th, 12:32 PM
Update values of one table based on condition of values in other table using Trigger Gladiator 3 February 29th, 06:01 PM