Oracle FAQ Your Portal to the Oracle Knowledge Grid
HOME | ASK QUESTION | ADD INFO | SEARCH | E-MAIL US
 

Home -> Community -> Mailing Lists -> Oracle-L -> Re: DBMS_METADATA to get dependent DDL--solved

Re: DBMS_METADATA to get dependent DDL--solved

From: Paul Baumgartel <paul.baumgartel_at_gmail.com>
Date: Thu, 15 Dec 2005 20:17:07 -0500
Message-ID: <f8c47710512151717t21392353x490e6e6cb89643db@mail.gmail.com>


This is what I get for diving in to PL/SQL programming with nested tables before I really understood how they work: the problem was not with dbms_metadata, but with the way I was handling its output, and with the program logic. I was unclear about how fetch_ddl populated the sys.ku$_ddlsnested table. Without going into excruciating detail, the following
function definition works (the cv_* variables are constants defined elsewhere):

  function get_dep_ddl (pi_table_name in varchar2,

                        pi_object_type in varchar2) return sys.ku$_ddls is
    v_localddls sys.ku$_ddls;
    v_outputddls sys.ku$_ddls := sys.ku$_ddls();
    v_handle number;
    v_transform_handle number;

    v_i integer := 1;
    v_query varchar2(4000);

  begin
    v_handle := dbms_metadata.open(pi_object_type);

    dbms_metadata.set_filter(v_handle, cv_btyp_filter_name, cv_table);
    dbms_metadata.set_filter(v_handle, cv_bobj_filter_name, pi_table_name);
    v_transform_handle := dbms_metadata.add_transform(v_handle,
cv_transform_name);

    loop

      v_localddls := dbms_metadata.fetch_ddl(v_handle);
      exit when v_localddls is null;
      v_outputddls.extend;
      v_outputddls(v_i) := v_localddls(1);
      v_i := v_i + 1;

    end loop;
    return v_outputddls;
  end get_dep_ddl;

On 12/15/05, Paul Baumgartel <paul.baumgartel_at_gmail.com> wrote:
>
> 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_at_gmail.com> 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_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):
> > >
> >
>
>
>
> --
> Paul Baumgartel
> paul.baumgartel_at_aya.yale.edu
>
>

--
Paul Baumgartel
paul.baumgartel_at_aya.yale.edu

--
http://www.freelists.org/webpage/oracle-l
Received on Thu Dec 15 2005 - 19:17:21 CST

Original text of this message

HOME | ASK QUESTION | ADD INFO | SEARCH | E-MAIL US