You Asked
How could I filter the results in order for it to produce only certain alter statements. For example, I do not want to include changes in initrans and table storage in the result.
This is part of the code:
c:=dbms_metadata_diff.compare_alter(tipo,'object name' ,'object name' ,schema,schema,db_link1 , db_link2);
if length(c) > 0
then
detalle:= dbms_lob.substr(c,4000,1);
insert into detalle_objetos_diferentes
(cod_inf,categoria,owner,object_name,object_type,detalle)
values (id_informe,categoria, esquema ,l_src_record(indx).l_OBJECT_NAME, tipo,detalle);
end if;
commit;
and Connor said...
By default you are getting a CLOB back
SQL> select DBMS_METADATA_DIFF.COMPARE_ALTER('TABLE','EMP','EMP2') from dual;
DBMS_METADATA_DIFF.COMPARE_ALTER('TABLE','EMP','EMP2')
--------------------------------------------------------------------------------
ALTER TABLE "SCOTT"."EMP" ADD ("TS" TIMESTAMP(6))
ALTER TABLE "SCOTT"."EMP" ADD ("TSZ" TIMESTAMP (6) WITH TIME ZONE)
ALTER TABLE "SCOTT"."EMP" ADD ("RW" RAW(20))
ALTER TABLE "SCOTT"."EMP" ADD ("RID" ROWID)
ALTER TABLE "SCOTT"."EMP" DROP CONSTRAINT "EMP_PK"
ALTER TABLE "SCOTT"."EMP" DROP CONSTRAINT "EMP_FK"
ALTER TABLE "SCOTT"."EMP" INITRANS 10
ALTER TABLE "SCOTT"."EMP" RENAME TO "EMP2"
1 row selected.but you can also choose to get an XML back, which you can then parse out into invidual rows
SQL> with t as
2 (
3 select DBMS_METADATA_DIFF.COMPARE_ALTER_XML('TABLE','EMP','EMP2') xml from dual
4 )
5 select xt.*
6 from t,
7 xmltable(xmlnamespaces(default 'http://xmlns.oracle.com/ku'), '/ALTER_XML/ALTER_LIST/ALTER_LIST_ITEM/SQL_LIST/SQL_LIST_ITEM'
8 passing xmltype(t.xml)
9 columns
10 txt varchar2(255) path 'TEXT'
11 ) xt;
TXT
----------------------------------------------------------------------------------------------------------------------------------
ALTER TABLE "SCOTT"."EMP" ADD ("TS" TIMESTAMP(6))
ALTER TABLE "SCOTT"."EMP" ADD ("TSZ" TIMESTAMP (6) WITH TIME ZONE)
ALTER TABLE "SCOTT"."EMP" ADD ("RW" RAW(20))
ALTER TABLE "SCOTT"."EMP" ADD ("RID" ROWID)
ALTER TABLE "SCOTT"."EMP" DROP CONSTRAINT "EMP_PK"
ALTER TABLE "SCOTT"."EMP" DROP CONSTRAINT "EMP_FK"
ALTER TABLE "SCOTT"."EMP" INITRANS 10
ALTER TABLE "SCOTT"."EMP" RENAME TO "EMP2"
8 rows selected.
Once you have a row per command, you can use whatever filtering suits your needs, eg
SQL> with t as
2 (
3 select DBMS_METADATA_DIFF.COMPARE_ALTER_XML('TABLE','EMP','EMP2') xml from dual
4 )
5 select *
6 from (
7 select xt.*
8 from t,
9 xmltable(xmlnamespaces(default 'http://xmlns.oracle.com/ku'), '/ALTER_XML/ALTER_LIST/ALTER_LIST_ITEM/SQL_LIST/SQL_LIST_ITEM'
10 passing xmltype(t.xml)
11 columns
12 txt varchar2(255) path 'TEXT'
13 ) xt
14 )
15 where txt not like '%INITRA%'
16 and txt not like '%PCTFREE%';
TXT
----------------------------------------------------------------------------------------------------------------------------------
ALTER TABLE "SCOTT"."EMP" ADD ("TS" TIMESTAMP(6))
ALTER TABLE "SCOTT"."EMP" ADD ("TSZ" TIMESTAMP (6) WITH TIME ZONE)
ALTER TABLE "SCOTT"."EMP" ADD ("RW" RAW(20))
ALTER TABLE "SCOTT"."EMP" ADD ("RID" ROWID)
ALTER TABLE "SCOTT"."EMP" DROP CONSTRAINT "EMP_PK"
ALTER TABLE "SCOTT"."EMP" DROP CONSTRAINT "EMP_FK"
ALTER TABLE "SCOTT"."EMP" RENAME TO "EMP2"
7 rows selected.