CODEWITHSUNDEEP
sqlsql-server-2005oracle-databaseoracle10gdata-migration
Dels
Dels

Reputation: 2425

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

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

Upvotes: 1

Views: 6080

Answers (4)

Jesus A. Sanchez
Jesus A. Sanchez

Reputation: 146

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.

Upvotes: 1

devio
devio

Reputation: 37225

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

Upvotes: 1

Pop
Pop

Reputation: 4032

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

Upvotes: 3

Mark Harrison
Mark Harrison

Reputation: 304662

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

Upvotes: 2

Related Questions