DBMS_METADATA: Get table SXML with triggers

201 Views Asked by At

I want to read the SXML representation of a table using this simple function:

CREATE OR REPLACE FUNCTION get_table_sxml(name IN VARCHAR2) RETURN CLOB IS
   open_handle          NUMBER;
   transform_handle     NUMBER;
   doc                  CLOB;
BEGIN
   open_handle := DBMS_METADATA.OPEN('TABLE');
   DBMS_METADATA.SET_FILTER(open_handle,'NAME',name);
   transform_handle := DBMS_METADATA.ADD_TRANSFORM(open_handle, 'SXML');
   dbms_metadata.set_transform_param(transform_handle,'REF_CONSTRAINTS', true);
   dbms_metadata.set_transform_param(transform_handle,'CONSTRAINTS', true);
   doc := DBMS_METADATA.FETCH_CLOB(open_handle);
   DBMS_METADATA.CLOSE(open_handle);
   RETURN doc;
END;

When i select the generated XML with "SELECT get_table_sxml('TABLENAME') FROM dual" i get the complete xml representation, including constraints and ref constraints.

However the triggers associated with this table are missing in the output.

Can anyone give me a hint what i have to do to get the triggers into the output-xml?

The resulting XML is supposed to be used on another database to compare the tables and build diff-scripts using the DBMS_METADATA-package. So i need to use the "sxml" format.

0

There are 0 best solutions below