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;
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@xxxxxxxxxxxx
Other related posts:
