Skip to Main Content
  • Questions
  • DBMS_METADATA.GET_DDL for table with foreign key

Breadcrumb

Question and Answer

Tom Kyte

Thanks for the question, Michael.

Asked: August 03, 2010 - 6:36 pm UTC

Last updated: August 05, 2010 - 7:41 am UTC

Version: Oracle 10.2.0

Viewed 1000+ times

You Asked

I reverse-engineered DDL CREATE TABLE scripts for entire schema to apply them against another (empty) schema. Scripts were generated correctly (code below), but because the foreign key is part of generated CREATE TABLE DDL I cannot apply scripts in the order how CREATE TABLE were generated in the file.
Looks like I need to create first the tables which are not having foreign keys. How to force GET_DDL generate CREATE TABLE statements in right order?
Or may be exists the better way?

Example below:
execute DBMS_METADATA.SET_TRANSFORM_PARAM (DBMS_METADATA.SESSION_TRANSFORM,'SQLTERMINATOR',true)

spool DDL-table.txt
SELECT DBMS_METADATA.GET_DDL(object_type,object_name) FROM USER_OBJECTS WHERE OBJECT_TYPE = 'TABLE'AND STATUS='VALID';
spool off


CREATE TABLE ZZZ.A (
I NUMBER,
J NUMBER,
PRIMARY KEY I,
FOREIGN KEY J REFERENCES ZZZ.B (J) ON DELETE CASCADE ENABLE);

CREATE TABLE ZZZ.B ( J NUMBER, PRIMARY KEY J);

Thanks!
Michael.




and Tom said...


Is this answer out of date? If it is, please let us know via a Comment

More to Explore

DBMS_METADATA

More on PL/SQL routine DBMS_METADATA here