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.