Re: DBMS_METADATA to get dependent DDL

From: Ethan Post <post.ethan_at_gmail.com>
Date: Thu, 15 Dec 2005 16:03:51 -0600
Message-ID: <357b48a90512151403u61e014c8xc94bfc02621139e9@mail.gmail.com>

Don't have time to look too close but perhaps this script will work for you, this is what I required to generate some PK/FK related DDL. It created some scripts which I could use to drop, rebuild some constraints for a particular issue I was working on.

Call it like this...

@script.sql TABLE_NAME

set echo off feed off pages 0 trims on term off trim on set long 500000
set heading off
set linesize 255
set ver off
set term on

column ddl format a200 word_wrapped

spool ~metadata.sql

select 'select dbms_metadata.get_ddl(''REF_CONSTRAINT'','''||a.constraint_name||''')||'';'' ddl from dual;'
  from

       user_constraints a,
       user_constraints b
 where a.constraint_type='R'
   and a.r_constraint_name=b.constraint_name

   and b.constraint_type='P'
   and b.table_name='&1';

spool off

spool make_fk.sql
@~metadata.sql
spool off

spool drop_fk.sql

select 'alter table '||a.table_name||' drop constraint '||a.constraint_name||';' ddl
  from

       user_constraints a,
       user_constraints b
 where a.constraint_type='R'
   and a.r_constraint_name=b.constraint_name

   and b.constraint_type='P'
   and b.table_name='&1';

spool off

spool ~metadata.sql

select 'select dbms_metadata.get_ddl(''CONSTRAINT'','''||a.constraint_name||''')||'';'' ddl from dual;'
  from

       user_constraints a
 where a.constraint_type='P'
   and a.table_name='&1';

spool off

spool pk.sql

select 'alter table &1 drop primary key;' ddl from dual;

@~metadata.sql

spool off

!rm ~metadata.sql

On 12/15/05, Paul Baumgartel <paul.baumgartel_at_gmail.com> wrote:
> I've been struggling with this for a day. I am trying to get dependent DDL
> (constraints) for a table, and have written the following function to do it
> (using literals for testing):
>

--
http://www.freelists.org/webpage/oracle-l

Received on Thu Dec 15 2005 - 16:04:44 CST