generate database & tables schema (ddl) on Oracle pl-sql

Question

Hi,

Anyone have a PL-SQL statement that i can use to generate database & tables schema for specific database on Oracle 10g? I need the schema in .sql file and if possible compatible with ANSI-92/99 sql implementation, so i can use the generated .sql directly on sql server 2005.

Already heard about exp/imp, but it seems generated dump file, what i need just a simple ddl on .sql file.

Thanks

Answer 1

If you just need to dump your schema, this free package does a very nice job. We use it in daily production.

http://sourceforge.net/projects/cx-oracletools

If you need to convert from Oracle to SQL Server, this software might do a better job. We've used it to convert between Oracle, MySql, and Postgreqsql.

http://www.spectralcore.com/fullconvert

Answer 2

You could try:

select dbms_metadata.get_ddl('TABLE',table_name,owner)
from dba_tables where owner='schema name';

It returns longs, so you may want to play with the long buffer.

More about dbms_metadata here: http://download.oracle.com/docs/cd/B19306_01/appdev.102/b14258/d_metada.htm

Answer 3

I wrote oraddlscript which calls dbms_metadata.get_ddl (Pop's answer) for each database object owned by a user and writes the DDL to a file.

Update: Answered comment

Answer 4

Greetings, I'd recomend using Oracle SQL Developer Data Modeler since it's from Oracle, it can read the DDL information directly from the Data Dictionary. It creates an ERD and then you can produce DDL for SQL Server 2000/2005, some versions of DB2 and Oracle 9i/10g/11g.