Re: batch generating of DDL for a set of tables

From: Thomas Kyte <tkyte_at_us.oracle.com>
Date: 20 Aug 2001 11:24:05 -0700
Message-ID: <9lrkk50o6g@drn.newsguy.com>

In article <3B81934B.5080509_at_seul.org>, marko says...
>
>Thomas Kyte wrote:
>> In article <3B816187.3060403_at_seul.org>, marko says...
>>
>>>Hello,
>>>
>>>I use Oracle 9i for linux. With oemapp dbastudio I can
>>>generate DDL for a table and then save the sql in a file,
>>>but only one table at time. I cannot select multiple tables.
>>>
>>>Is it possible to generate sql file/s with DDL of a set of tables
>>>at once.
>>>
>>>I'd prefer to use a script for that.
>>>
>>>Thanks
>>>
>>>
>>>Marko
>>>
>>>
>>
>> See dbms_metadata:
>>
>>scott_at_ORA9I.WORLD> select dbms_metadata.get_ddl(object_type, object_name, user)
>> 2 from user_objects where object_type in ( 'TABLE', 'INDEX' );
>>
>> DBMS_METADATA.GET_DDL(OBJECT_TYPE,OBJECT_NAME,USER)
>
>This seems to me the cleanest way. I have tried various perl scripts
>but they missed informations about constrains and keys.
>
>Just a couple of questions if someone has patience with me:
>
>- the get_dll function returns a clob. How can I dump it to a file
>from within sqlplus (or procedure) (no perl please, I want to learn sql!) ?
>
>- is there a similar way to dump plsql procedures and functions
> ?
>

Lets say you want to extract the EMP table, FOO function, and EMP_IDX index -- you could code a sql script that looks like this:

---

set long 500000
set heading off
set linesize 255
column ddl format a200 word_wrapped
spool extract.sql

select dbms_metadata.get_ddl( object_type, object_name, user ) ||

                                                       chr(10) || '/' ddl

from user_objects
where object_name in ( 'EMP', 'FOO', 'EMP_IDX' ) order by decode(object_type, 'TABLE', 1, 'INDEX', 2, 'FUNCTION', 3 );

spool off
set heading on

---

When you run that:

SQL> @test

you'll have a file extract.sql that has in it:

CREATE TABLE "SCOTT"."EMP"
( "EMPNO" NUMBER(4,0) NOT NULL ENABLE,

"ENAME" VARCHAR2(10),
"JOB" VARCHAR2(9),
"MGR" NUMBER(4,0),
"HIREDATE" DATE,
"SAL" NUMBER(7,2),
"COMM" NUMBER(7,2),
"DEPTNO" NUMBER(2,0),

CONSTRAINT "EMP_PK" PRIMARY KEY ("EMPNO") USING INDEX PCTFREE 10 INITRANS 2 MAXTRANS 255 STORAGE(INITIAL 524288 NEXT 524288 MINEXTENTS 1 MAXEXTENTS 2147483645 PCTINCREASE 0
FREELISTS 1 FREELIST GROUPS 1 BUFFER_POOL DEFAULT) TABLESPACE "USERS" ENABLE,

CONSTRAINT "EMP_FK_EMP" FOREIGN KEY ("MGR")
REFERENCES "SCOTT"."EMP" ("EMPNO") ENABLE NOVALIDATE,
CONSTRAINT "EMP_FK_DEPT" FOREIGN KEY ("DEPTNO")
REFERENCES "SCOTT"."DEPT" ("DEPTNO") ENABLE NOVALIDATE

) PCTFREE 10 PCTUSED 40 INITRANS 1 MAXTRANS 255 LOGGING STORAGE(INITIAL 524288 NEXT 524288 MINEXTENTS 1 MAXEXTENTS 2147483645 PCTINCREASE 0
  FREELISTS 1 FREELIST GROUPS 1 BUFFER_POOL DEFAULT) TABLESPACE "USERS"   /

CREATE INDEX "SCOTT"."EMP_IDX" ON "SCOTT"."EMP" ("DEPTNO") PCTFREE 10 INITRANS 2 MAXTRANS 255
STORAGE(INITIAL 524288 NEXT 524288 MINEXTENTS 1 MAXEXTENTS 2147483645 PCTINCREASE 0
FREELISTS 1 FREELIST GROUPS 1 BUFFER_POOL DEFAULT) TABLESPACE "USERS"   /

CREATE OR REPLACE FUNCTION "SCOTT"."FOO" return number as
begin
return 55;
end;
/

This of course works only in 9i and up which has the dbms_metadata package and where string concatenation to a CLOB works like that.

--
Thomas Kyte (tkyte@us.oracle.com)             http://asktom.oracle.com/ 
Expert one on one Oracle, programming techniques and solutions for Oracle.
http://www.amazon.com/exec/obidos/ASIN/1861004826/  
Opinions are mine and do not necessarily reflect those of Oracle Corp 

Received on Mon Aug 20 2001 - 13:24:05 CDT