43073

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

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>

Answer3:

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.

Recommend

  • Alembic trying to delete my tables
  • vb.net dropdownlist populate
  • Accents not getting inserted in SQL server
  • Try Catch Can't handle alter table
  • Oracle's specification or SQL's ? : Truncate table with foreign key constraints
  • Transfer mysql data to a new Database structure
  • find a control by id from Dynamically added Template field in GridView
  • org.h2.jdbc.JdbcSQLException: Column “ID” not found
  • Can GRANT be used inside an Oracle Store Procedure?
  • generate database & tables schema (ddl) on Oracle pl-sql
  • Playframework evolutions files compatible with both postgres and h2
  • Difference between @Size, @Length and @Column(length=value) when using JPA and Hibernate
  • how can I remove codes creating session in unit test for Play framework and slick
  • Can't access delete method on Slick query
  • Optimize access query with nested IIF's
  • Joining value(list) from in dictionary
  • Setting @Table in run time Spring
  • Why does calling next on an Iterator trait object give me an “out of memory” at runtime?
  • Migration scripts for Structure and Data
  • Why does PostgresSQL reassign role command, change template database's owner
  • How can I get all open graph pages owned by an application?
  • What is wrong with this emulation of CMPXCHG16B instruction?
  • How do I translate LR(1) Parse into a Abstract syntax tree?
  • How can I get the full list of running processes on a Mac from a python app
  • Detecting null parameter in preprocessor macro
  • Alert pop up with LWUIT
  • Javascript convert timezone issue
  • Jquery - Jquery Wysiwyg return html as a string
  • AT Commands to Send SMS not working in Windows 8.1
  • Arrays break string types in Julia
  • Rails 2: use form_for to build a form covering multiple objects of the same class
  • WPF Applying a trigger on binding failure
  • How can I get HTML syntax highlighting in my editor for CakePHP?
  • How do I configure my settings file to work with unit tests?
  • Java static initializers and reflection
  • Authorize attributes not working in MVC 4
  • Is it possible to post an object from jquery to bottle.py?
  • Busy indicator not showing up in wpf window [duplicate]
  • Python/Django TangoWithDjango Models and Databases
  • Net Present Value in Excel for Grouped Recurring CF