Re: Is it possible to dump oracle database to DDL/SQL syntax

I'm not a dba so I don't know if this is bad or good to do, but here is what I have done to get my DDL:

IN Sql*plus, first turn off headings and unwanted feedback and set a workable linesize:

set heading off
set linesize 132
set feedback off

Then, if you want to spool this to a file, turn on the spool option.

Finally, run the following code:

SELECT DECODE(T1.COLUMN_ID,1,'CREATE TABLE ' || T1.TABLE_NAME || ' (',' ') A,
 T1.COLUMN_NAME B, T1.DATA_TYPE || DECODE(T1.DATA_TYPE,  'VARCHAR2', '('||TO_CHAR(T1.DATA_LENGTH)||')',  'NUMBER','('||TO_CHAR(T1.DATA_PRECISION)|| ','||TO_CHAR(T1.DATA_SCALE)||')',
 'CHAR','('||TO_CHAR(T1.DATA_LENGTH)||')')|| DECODE(T1.COLUMN_ID,MAX(T2.COLUMN_ID), ');',',') C FROM USER_TAB_COLUMNS T1, USER_TAB_COLUMNS T2 WHERE T1.TABLE_NAME = T2.TABLE_NAME
GROUP BY T1.COLUMN_ID, T1.TABLE_NAME, T1.DATA_TYPE,  T1.DATA_LENGTH, T1.DATA_SCALE, T1.COLUMN_NAME, T1.DATA_PRECISION ORDER BY T1.COLUMN_ID; This will generate the create table statements. Similarly, you can generate the drop table statements by changing the above.

If you wish to run this for a single table, just add to the where clause: AND T1.TABLENAME='mytable'
replacing mytable with your table name.

You'll also want to remember to turn the feedback and headings back on when you are done, or at least restate sql*plus.

Hope this helps!

--Rob Diaz

<fenton_travers_at_my-deja.com> wrote in message news:8o4vkc$hos$1_at_nnrp1.deja.com...
> I have an oracle database and I want to dump it to a ddl file. Ie a
> file that I could later use like:
>
> $ sqlplus < myData.ddl
>
> so it would have the table drop syntax, table create syntax and the
> insert into syntax.
>
> I can do this simply with MySQL but my Oracle DBA says I can't do this
> in oracle and I don't believe him.
>
> Fenton
>
>
> Sent via Deja.com http://www.deja.com/
> Before you buy.
Received on Fri Aug 25 2000 - 05:46:15 CDT