43073
Question:
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
Answer1:I wrote <a href="http://devio.wordpress.com/2009/10/25/introducing-oraddlscript/" rel="nofollow">oraddlscript</a> 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: <a href="http://devio.wordpress.com/2009/11/04/oraddlscript-solving-odp-net-version-conflicts/" rel="nofollow">Answered comment</a>
Answer2: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: <a href="http://download.oracle.com/docs/cd/B19306_01/appdev.102/b14258/d_metada.htm" rel="nofollow">http://download.oracle.com/docs/cd/B19306_01/appdev.102/b14258/d_metada.htm</a>
If you just need to dump your schema, this free package does a very nice job. We use it in daily production.
<blockquote><a href="http://sourceforge.net/projects/cx-oracletools" rel="nofollow">http://sourceforge.net/projects/cx-oracletools</a>
</blockquote>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.
<blockquote><a href="http://www.spectralcore.com/fullconvert" rel="nofollow">http://www.spectralcore.com/fullconvert</a>
</blockquote> Answer4:Greetings, I'd recomend using <a href="http://www.oracle.com/technology/products/database/datamodeler/index.html" rel="nofollow">Oracle SQL Developer Data Modeler</a> 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.
