Geek Answers Handbook

Create database and table schema (ddl) in Oracle pl-sql

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

Already heard of exp / imp, but it looks like the generated dump file, I need a simple ddl for the .sql file.

thanks

+1
source share
4 answers

I wrote oraddlscript that calls dbms_metadata.get_ddl (pop response) for each database object owned by the user and writes the DDL to the file.

Update: Reply Comment

+1
source

You can try:

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

It returns longs, so you can play with a long buffer.

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

+3
source

If you just need to reset the circuit, this free package does a very good 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 may work better. We used it to convert between Oracle, MySql and Postgreqsql.

http://www.spectralcore.com/fullconvert

+2
source
Hi, I recommend using the Oracle SQL Developer Data Modeler , since it is from Oracle, it can read DDL information directly from the Data Dictionary. It creates ERD, and then you can create DDL for SQL Server 2000/2005, some versions of DB2 and Oracle 9i / 10g / 11g.
+1
source

More articles:


All Articles