How to programmatically generate DDL from Oracle database?

Question

I've got a monumentally tedious task that is to find several tables from a huge schema, and generate the DDL for these tables.

Say, I've got a schemaA has 1000 tables, I need to find if a tableA existed in this schemaA, if it does, generate the DDL and save it to filesystem, if don't, print it's name out or write it to a file. Any ideas?

Answer 1

The DBMS_METADATA package (assuming you are on a reasonably recent version of Oracle) will generate the DDL for any object in the database. So

SELECT dbms_metadata.get_ddl( 'TABLE', 'TABLEA', 'SCHEMAA' )
  FROM dual;

will return a CLOB with the DDL for SchemaA.TableA. You could catch the exception that is thrown that the object doesn't exist, but I would tend to suggest that you query the data dictionary (i.e. DBA_OBJECTS) to verify that there is a table named TableA in SchemaA, i.e.

SELECT COUNT(*) 
  FROM dba_objects
 WHERE owner = 'SCHEMAA'
   AND object_name = 'TABLEA'
   AND object_type = 'TABLE'

Note that if you don't have access to DBA_OBJECTS, you could use ALL_OBJECTS instead. The concern there, however, is that there may be a TableA in SchemaA that you don't have SELECT access on. That table would not appear in ALL_OBJECTS (which has all the objects that you have access to) but it would appear in DBA_OBJECTS (which has all the objects in the database regardless of your ability to access them).

You can then either write the DDL to a file or, if the count is 0, indicate that the object doesn't exist. From a stored procedure, you can use the UTL_FILE package to write to a file on the database server. If you are trying to write to a file on the client file system, you would need to use a language that has access to the client operating system's resources. A small C/ Java/ Perl/ etc. program should be able to select a CLOB and write that data to a file on the client operating system.