DBMS_METADATA to get dependent DDL

From: Paul Baumgartel <paul.baumgartel_at_gmail.com>
Date: Thu, 15 Dec 2005 16:52:13 -0500
Message-ID: <f8c47710512151352w1002e827i7a469fa44f5ba95@mail.gmail.com>

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):

  function get_dep_ddl (pi_table_name in varchar2,

                        pi_object_type in varchar2) return sys.ku$_ddls is
    v_ddls sys.ku$_ddls;

    v_handle number;
    v_transform_handle number;

  begin
    v_handle := dbms_metadata.open('CONSTRAINT');

    dbms_metadata.set_filter(v_handle, 'BASE_OBJECT_TYPE', 'TABLE');
    dbms_metadata.set_filter(v_handle, 'BASE_OBJECT_NAME', 'COMPANY');
    dbms_metadata.set_filter(v_handle,'SCHEMA', 'PB');
    dbms_metadata.set_filter(v_handle,'BASE_OBJECT_SCHEMA', 'PB');

    v_transform_handle := dbms_metadata.add_transform(v_handle, 'DDL');     loop

      v_ddls := dbms_metadata.fetch_ddl(v_handle);
      exit when v_ddls is null;

    end loop;
    return v_ddls;
  end get_dep_ddl;

The function doesn't return any DDL in the sys.ku$_ddls collection.

However, calling

dbms_metadata.get_dependent_ddl('CONSTRAINT','COMPANY')

does return the constraint DDL. What am I missing here?

I've examined the output of DBMS_METADATA.GET_QUERY (called before the FETCH_DDL call), and compared it to the query used by get_dependent_ddl (determined
that one by tracing my session). They look identical and a trace with binds=true showed the bind values to be the same.

Suggestions (and especially solutions!) appreciated.

Thanks,

--
Paul Baumgartel
paul.baumgartel_at_aya.yale.edu

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

Received on Thu Dec 15 2005 - 15:54:02 CST