Simple example
create table tq84_diff_a (
a number primary key,
c number,
b varchar2(10)
);
create table tq84_diff_b (
a number,
c number,
b varchar2(12),
--
primary key(a, c)
);
declare
xml_from clob;
xml_to clob;
res clob;
fc clob;
hdl number;
doc_h1 number;
doc_h2 number;
begin
xml_from := dbms_metadata.get_sxml('TABLE', 'TQ84_DIFF_A');
xml_to := dbms_metadata.get_sxml('TABLE', 'TQ84_DIFF_B');
hdl := dbms_metadata_diff.openc('TABLE');
dbms_metadata_diff.add_document(handle => hdl, document => xml_from);
dbms_metadata_diff.add_document(handle => hdl, document => xml_to );
fc := dbms_metadata_diff.fetch_clob(hdl);
dbms_metadata_diff.close(hdl);
hdl := dbms_metadata.openw('TABLE');
doc_h1 := dbms_metadata.add_transform(hdl, 'ALTERXML');
doc_h2 := dbms_metadata.add_transform(hdl, 'ALTERDDL');
dbms_lob.createtemporary(res, true);
dbms_metadata.convert(hdl, fc , res);
dbms_metadata.close(hdl);
dbms_output.put_line(res);
end;
/
drop table tq84_diff_a purge;
drop table tq84_diff_b purge;
When executed, this example prints
ALTER TABLE "RENE"."TQ84_DIFF_A" MODIFY ("B" VARCHAR2(12 CHAR))
ALTER TABLE "RENE"."TQ84_DIFF_A" DROP PRIMARY KEY
ALTER TABLE "RENE"."TQ84_DIFF_A" ADD PRIMARY KEY ("A","C") USING INDEX PCTFREE 10
INITRANS 2 ENABLE
ALTER TABLE "RENE"."TQ84_DIFF_A" RENAME TO "TQ84_DIFF_B"
SXML is an
XML format to describe metadata of
Oracle objects. The structure of SXML corresponds to the SQL clauses with which the objects were created.
SXML was specifically designed for object editioning and comparison purposes.
SXML it is different from the XML that is returned by
dbms_metadata.get_xml (The value returned by
get_xml is more complex).
In order to convert between XML and SXML or from SXML to a DDL statement, a transformation must be enabled with
dbms_metadata.add_transform.