utPLSQL · jgebal · Apr 22, 2018 · Apr 20, 2018 · Apr 21, 2018 · Apr 21, 2018
diff --git a/docs/userguide/advanced_data_comparison.md b/docs/userguide/advanced_data_comparison.md
@@ -29,6 +29,7 @@ Each item in the comma separated list can be:
 - an attribute name of object type to be compared   
 - an attribute name of object type within a table of objects to be compared
 - an [XPath](http://zvon.org/xxl/XPathTutorial/Output/example1.html) expression representing column/attribute
+- Include and exclude option will not support implicit colum names that starts with single quota, or in fact any other special characters e.g. <, >, &
 
 Each element in `ut_varchar2_list` nested table can be an item or a comma separated list of items.
 

diff --git a/source/core/ut_utils.pkb b/source/core/ut_utils.pkb
@@ -544,5 +544,19 @@ procedure append_to_clob(a_src_clob in out nocopy clob, a_clob_table t_clob_tab,
     return l_filtered_list;
   end;
 
+  function xmlgen_escaped_string(a_string in varchar2) return varchar2 is
+    l_result varchar2(4000);
+    l_sql varchar2(32767) := q'!select q'[!'||a_string||q'!]' as "!'||a_string||'" from dual';
+  begin
+    if a_string is not null then
+      select extract(dbms_xmlgen.getxmltype(l_sql),'/*/*/*').getRootElement() 
+      into l_result
+      from dual;
+    else
+    l_result := a_string;
+    end if;
+    return l_result;
+  end;
+
 end ut_utils;
 /
diff --git a/source/core/ut_utils.pks b/source/core/ut_utils.pks
@@ -302,5 +302,8 @@ create or replace package ut_utils authid definer is
   /*It takes a collection of type ut_varchar2_list and it only returns the elements which meets the regular expression*/
   function filter_list(a_list IN ut_varchar2_list, a_regexp_filter in varchar2) return ut_varchar2_list;
 
+  -- Generates XMLGEN escaped string
+  function xmlgen_escaped_string(a_string in varchar2) return varchar2;
+
 end ut_utils;
 /
diff --git a/source/expectations/data_values/ut_compound_data_helper.pkb b/source/expectations/data_values/ut_compound_data_helper.pkb
@@ -55,7 +55,12 @@ create or replace package body ut_compound_data_helper is
       a_cursor := dbms_sql.to_refcursor( l_cursor_number );
       l_columns_tab := get_columns_info( l_columns_desc, l_columns_count);
 
-      select XMLELEMENT("ROW", xmlagg(xmlelement(evalname key, value))) into l_result from table(l_columns_tab );
+      select 
+        XMLELEMENT("ROW", xmlagg(xmlelement(evalname ut_utils.xmlgen_escaped_string(key),
+                                           XMLATTRIBUTES(key AS "xml_valid_name"), 
+                                           value)))
+      into l_result 
+      from table(l_columns_tab );
 
       return l_result;
     end;
@@ -89,31 +94,41 @@ create or replace package body ut_compound_data_helper is
     l_sql            varchar2(32767);
     l_results        tt_column_diffs;
   begin
-    l_column_filter := ut_compound_data_helper.get_columns_filter(a_exclude_xpath, a_include_xpath);
+    l_column_filter := get_columns_filter(a_exclude_xpath, a_include_xpath);
     l_sql := q'[
       with
         expected_cols as ( select :a_expected as item_data from dual ),
         actual_cols as ( select :a_actual as item_data from dual ),
-        expected_cols_info as (
+  expected_cols_info as (
           select e.*,
                  replace(expected_type,'VARCHAR2','CHAR') expected_type_compare
             from (
-              select rownum expected_pos,
-                     r.column_value.getrootelement() expected_name,
-                     extractvalue(r.column_value,'/*') expected_type
-              from ( select ]'||l_column_filter||q'[ from expected_cols ucd ) s,
-                table( xmlsequence(extract(s.item_data,'/*/*')) ) r
+                   SELECT rownum expected_pos,
+                   xt.name expected_name,
+                   xt.type expected_type
+         FROM   (select ]'||l_column_filter||q'[ from expected_cols ucd) x,
+           XMLTABLE('/ROW/*'
+             PASSING x.item_data
+             COLUMNS 
+               name     VARCHAR2(4000)  PATH '@xml_valid_name',
+               type      VARCHAR2(4000) PATH '/' 
+             ) xt
             ) e
         ),
         actual_cols_info as (
           select a.*,
                  replace(actual_type,'VARCHAR2','CHAR') actual_type_compare
             from (
-              select rownum actual_pos,
-                     r.column_value.getrootelement() actual_name,
-                     extractvalue(r.column_value,'/*') actual_type
-              from ( select ]'||l_column_filter||q'[ from actual_cols ucd ) s,
-                table( xmlsequence(extract(s.item_data,'/*/*')) ) r
+                   SELECT rownum actual_pos,
+                   xt.name actual_name,
+                   xt.type actual_type
+         FROM    (select ]'||l_column_filter||q'[ from actual_cols ucd) x,
+           XMLTABLE('/ROW/*'
+             PASSING x.item_data
+             COLUMNS 
+               name     VARCHAR2(4000)  PATH '@xml_valid_name',
+               type      VARCHAR2(4000) PATH '/' 
+             ) xt
             ) a
         ),
         joined_cols as (
@@ -152,7 +167,7 @@ create or replace package body ut_compound_data_helper is
     l_column_filter varchar2(32767);
     l_results       tt_row_diffs;
   begin
-    l_column_filter := get_columns_filter(a_exclude_xpath, a_include_xpath);
+    l_column_filter := get_columns_filter(a_exclude_xpath,a_include_xpath);
     execute immediate q'[
       with
         diff_info as (select item_no from ut_compound_data_diff_tmp ucdc where diff_id = :diff_guid and rownum <= :max_rows)
@@ -231,14 +246,14 @@ create or replace package body ut_compound_data_helper is
     a_hash_type binary_integer := dbms_crypto.hash_sh1
   ) return t_hash is
     l_cols_hash t_hash;
-  begin
-    if not a_data_value_cursor.is_null then
+  begin      
+    if not a_data_value_cursor.is_null then      
       execute immediate
       q'[select dbms_crypto.hash(replace(x.item_data.getclobval(),'>CHAR<','>VARCHAR2<'),]'||a_hash_type||') ' ||
       '  from ( select '||get_columns_filter(a_exclude_xpath, a_include_xpath)||
       '           from (select :columns_info as item_data from dual ) ucd' ||
       '  ) x'
-      into l_cols_hash using a_exclude_xpath, a_include_xpath, a_data_value_cursor.columns_info;
+      into l_cols_hash using a_exclude_xpath,a_include_xpath, a_data_value_cursor.columns_info;
     end if;
     return l_cols_hash;
   end;
@@ -263,5 +278,6 @@ begin
   g_type_name_map( dbms_sql.number_type )                  := 'NUMBER';
   g_type_name_map( dbms_sql.rowid_type )                   := 'ROWID';
   g_type_name_map( dbms_sql.urowid_type )                  := 'UROWID';
+
 end;
 /
diff --git a/source/expectations/data_values/ut_data_value_refcursor.tpb b/source/expectations/data_values/ut_data_value_refcursor.tpb
@@ -140,7 +140,7 @@ create or replace type body ut_data_value_refcursor as
       for i in 1 .. a_column_diffs.count loop
         if a_column_diffs(i).diff_type in ('-','+') then
           l_incomparable_cols.extend;
-          l_incomparable_cols(l_incomparable_cols.last) := coalesce(a_column_diffs(i).expected_name,a_column_diffs(i).actual_name);
+          l_incomparable_cols(l_incomparable_cols.last) := ut_utils.xmlgen_escaped_string(coalesce(a_column_diffs(i).expected_name,a_column_diffs(i).actual_name));
         end if;
       end loop;
       l_result := ut_utils.to_xpath(l_incomparable_cols);

diff --git a/test/core/expectations/compound_data/test_expectations_cursor.pkb b/test/core/expectations/compound_data/test_expectations_cursor.pkb
@@ -963,5 +963,83 @@ Rows: [ 4 differences ]
     ut.expect(ut3.ut_expectation_processor.get_warnings()(1)).to_be_like('The syntax: "%" is deprecated.%');
   end;
 
+  procedure col_diff_on_col_name_implicit is
+    l_actual   SYS_REFCURSOR;
+    l_expected SYS_REFCURSOR;
+    l_actual_message   varchar2(32767);
+    l_expected_message varchar2(32767);
+  begin
+    --Arrange
+    open l_actual   for select '1' , '2'      from dual connect by level <=2;
+    open l_expected for select rownum , rownum expected_column_name from dual connect by level <=2;
+    --Act
+    ut3.ut.expect(l_actual).to_equal(l_expected);
+
+    l_expected_message := q'[Actual: refcursor [ count = 2 ] was expected to equal: refcursor [ count = 2 ]%
+Diff:%
+Columns:%
+  Column <ROWNUM> [data-type: NUMBER] is missing. Expected column position: 1.%
+  Column <EXPECTED_COLUMN_NAME> [data-type: NUMBER] is missing. Expected column position: 2.%
+  Column <%1%> [position: 1, data-type: CHAR] is not expected in results.%
+  Column <%2%> [position: 2, data-type: CHAR] is not expected in results.%
+Rows: [ 2 differences ]%
+  All rows are different as the columns are not matching.%]';
+    l_actual_message := ut3.ut_expectation_processor.get_failed_expectations()(1).message;
+    --Assert
+    ut.expect(l_actual_message).to_be_like(l_expected_message);
+  end;
+
+  procedure col_mtch_on_col_name_implicit is
+    l_actual   SYS_REFCURSOR;
+    l_expected SYS_REFCURSOR;
+    l_actual_message   varchar2(32767);
+    l_expected_message varchar2(32767);
+  begin
+    --Arrange
+    open l_actual   for select '1' , rownum  from dual connect by level <=2;
+    open l_expected for select '1' , rownum  from dual connect by level <=2;
+    --Act
+    ut3.ut.expect(l_actual).to_equal(l_expected);
+    --Assert
+    ut.expect(expectations.failed_expectations_data()).to_be_empty();
+  end;
+
+
+  procedure include_col_name_implicit is
+    l_actual   SYS_REFCURSOR;
+    l_expected SYS_REFCURSOR;
+  begin
+    --Arrange
+    open l_actual   for select rownum as rn, 'a', 'c' as A_COLUMN, 'x' SOME_COL, 'd' "Some_Col" from dual a connect by level < 4;
+    open l_expected for select rownum as rn, 'a', 'd' as A_COLUMN, 'x' SOME_COL, 'c' "Some_Col" from dual a connect by level < 4;
+    begin
+      --Act
+      ut3.ut.expect(l_actual).to_equal(l_expected).include(q'!/ROW/RN,'a',//SOME_COL!');
+      --Assert
+      ut.fail('Expected exception but nothing was raised');
+    exception
+      when others then
+        ut.expect(sqlcode).to_be_between(-31013,-31011);
+    end;
+  end;
+
+  procedure exclude_col_name_implicit is
+    l_actual   SYS_REFCURSOR;
+    l_expected SYS_REFCURSOR;
+  begin
+    --Arrange
+    open l_actual   for select rownum as rn, 'a', 'c' as A_COLUMN, 'x' SOME_COL, 'd' "Some_Col" from dual a connect by level < 4;
+    open l_expected for select rownum as rn, 'a', 'd' as A_COLUMN, 'x' SOME_COL, 'c' "Some_Col" from dual a connect by level < 4;
+    begin
+      --Act
+      ut3.ut.expect(l_actual).to_equal(l_expected).exclude(q'!/ROW/RN,'a',//SOME_COL!');
+      --Assert
+      ut.fail('Expected exception but nothing was raised');
+    exception
+      when others then
+        ut.expect(sqlcode).to_be_between(-31013,-31011);
+    end;
+  end;
+
 end;
 /
diff --git a/test/core/expectations/compound_data/test_expectations_cursor.pks b/test/core/expectations/compound_data/test_expectations_cursor.pks
@@ -187,7 +187,17 @@ create or replace package test_expectations_cursor is
   --%test(Adds a warning when using depreciated syntax to_( equal( a_expected sys_refcursor, a_exclude ut_varchar2_list )) )
   procedure deprec_equal_excl_list;
 
-  --%test(Reports column name differences if found)
-
+  --%test(Reports column diff on cursor with column name implicit )
+  procedure col_diff_on_col_name_implicit;
+
+  --%test(Reports column match on cursor with column name implicit )
+  procedure col_mtch_on_col_name_implicit;
+
+  --%test( Fail on passing implicit column name as include filter )
+  procedure include_col_name_implicit;
+
+  --%test( Fail on passing implicit column name as exclude filter )
+  procedure exclude_col_name_implicit;
+
 end;
 /