Re: DBMS_METADATA to get dependent DDL
- From: Paul Baumgartel <paul.baumgartel@xxxxxxxxx>
- To: Ethan Post <post.ethan@xxxxxxxxx>
- Date: Thu, 15 Dec 2005 17:11:47 -0500
Thanks. The reason I'm using the fetch_ddl interface is that this is going
to be part of a package--I'm recreating some non-partitioned tables as
partitioned, and I want to grab the constraint and trigger DDL so I can
apply it later.
On 12/15/05, Ethan Post <post.ethan@xxxxxxxxx> wrote:
>
> 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@xxxxxxxxx> 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):
> >
>
--
Paul Baumgartel
paul.baumgartel@xxxxxxxxxxxx
Other related posts:
