DBMS_METADATA to get dependent DDL

  • From: Paul Baumgartel <paul.baumgartel@xxxxxxxxx>
  • To: Oracle-L <oracle-l@xxxxxxxxxxxxx>
  • Date: Thu, 15 Dec 2005 16:52:13 -0500

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;

    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');
      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


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.

Paul Baumgartel

Other related posts: