Fixing issue with null vs null being returned as false.

Fixing change in Oracle 12.2+ where XML is stored as binary XML and not preserving insignificant white space. This require store data as clob.
utPLSQL · jgebal · Apr 1, 2019 · Mar 19, 2019 · Mar 27, 2019 · Mar 27, 2019
commit 4b9133e38b6b00faf602588bbc74522f0ddadd2b
diff --git a/source/expectations/data_values/ut_compound_data_helper.pkb b/source/expectations/data_values/ut_compound_data_helper.pkb
@@ -34,7 +34,7 @@ create or replace package body ut_compound_data_helper is
           ,position + x.item_no item_no
           {:columns:}
         from {:ut3_owner:}.ut_compound_data_tmp x,
-          xmltable('/ROWSET/ROW' passing x.item_data columns
+          xmltable('/ROWSET/ROW' passing xmltype(x.item_data) columns
             item_data xmltype path '*'
             ,position for ordinality
             {:xml_to_columns:} ) ucd
@@ -52,7 +52,7 @@ create or replace package body ut_compound_data_helper is
           ,position + x.item_no item_no 
           {:columns:}
         from {:ut3_owner:}.ut_compound_data_tmp x,
-          xmltable('/ROWSET/ROW' passing x.item_data columns 
+          xmltable('/ROWSET/ROW' passing xmltype(x.item_data) columns 
             item_data xmltype path '*'
             ,position for ordinality
             {:xml_to_columns:} ) ucd
@@ -174,7 +174,7 @@ create or replace package body ut_compound_data_helper is
 
   function generate_equal_sql(a_col_name in varchar2) return varchar2 is
   begin
-    return ' a.'||a_col_name||q'[ = ]'||' e.'||a_col_name;
+    return ' decode(a.'||a_col_name||','||' e.'||a_col_name||',1,0) = 1 ';
   end;
 
   function generate_partition_stmt(

diff --git a/source/expectations/data_values/ut_compound_data_tmp.sql b/source/expectations/data_values/ut_compound_data_tmp.sql
@@ -14,7 +14,7 @@ create global temporary table ut_compound_data_tmp(
   */
   data_id          raw(32),
   item_no          integer,
-  item_data        xmltype,
+  item_data        clob,
   item_hash        raw(128),
   pk_hash          raw(128),
   duplicate_no     integer,

diff --git a/source/expectations/data_values/ut_data_value_refcursor.tpb b/source/expectations/data_values/ut_data_value_refcursor.tpb
@@ -55,7 +55,7 @@ create or replace type body ut_data_value_refcursor as
       execute immediate
       'insert into ' || l_ut_owner || '.ut_compound_data_tmp(data_id, item_no, item_data) ' ||
       'values (:self_guid, :self_row_count, :l_xml)'
-      using in self.data_id, l_set_id, l_xml;           
+      using in self.data_id, l_set_id, l_xml.getclobval();           
       l_set_id := l_set_id + c_bulk_rows;   
     end loop;
     ut_expectation_processor.reset_nls_params();

diff --git a/test/core/expectations/test_expectation_anydata.pkb b/test/core/expectations/test_expectation_anydata.pkb
@@ -970,5 +970,29 @@ Rows: [ 60 differences, showing first 20 ]
     ut.expect(expectations.failed_expectations_data()).to_be_empty(); 
   end;  
 
+  procedure arr_empty_eq_arr_empty_unord is
+  begin
+   --Arrange
+   g_test_expected := anydata.convertCollection(t_tab_varchar(null));
+   g_test_actual   := anydata.convertCollection(t_tab_varchar(null));
+
+   --Act
+   ut3.ut.expect( g_test_actual ).to_equal( g_test_expected ).unordered();
+   ut.expect(expectations.failed_expectations_data()).to_be_empty();  
+
+  end;   
+
+  procedure arr_empty_nqua_arr_e_unord is
+  begin
+   --Arrange
+   g_test_expected := anydata.convertCollection(t_tab_varchar('t'));
+   g_test_actual   := anydata.convertCollection(t_tab_varchar(' '));
+
+   --Act
+   ut3.ut.expect( g_test_actual ).to_equal( g_test_expected ).unordered();
+   ut.expect(expectations.failed_expectations_data()).to_be_empty();  
+
+  end;  
+
 end;
 /
diff --git a/test/core/expectations/test_expectation_anydata.pks b/test/core/expectations/test_expectation_anydata.pks
@@ -198,5 +198,10 @@ create or replace package test_expectation_anydata is
   --%test( Success when anydata object contains data from another anydata)
   procedure object_to_contain;     
 
+  --%test ( Empty Array equal empty array )
+  procedure arr_empty_eq_arr_empty_unord;
+
+  --%test ( Empty Array not equal array with space )
+  procedure arr_empty_nqua_arr_e_unord;  
 end;
 /