utPLSQL · lwasylow · Apr 14, 2019 · Apr 12, 2019 · Apr 13, 2019 · Apr 14, 2019
diff --git a/source/core/ut_utils.pkb b/source/core/ut_utils.pkb
@@ -16,6 +16,13 @@ create or replace package body ut_utils is
   limitations under the License.
   */
 
+  /**
+  * Constants regex used to validate XML name
+  */
+  gc_invalid_first_xml_char  constant varchar2(50)  := '[^_a-zA-Z]';
+  gc_invalid_xml_char        constant varchar2(50)  := '[^_a-zA-Z0-9\.-]';
+  gc_full_valid_xml_name     constant varchar2(50)  := '^([_a-zA-Z])([_a-zA-Z0-9\.-])*$';
+
   function surround_with(a_value varchar2, a_quote_char varchar2) return varchar2 is
   begin
     return case when a_quote_char is not null then a_quote_char||a_value||a_quote_char else a_value end;
@@ -749,8 +756,47 @@ create or replace package body ut_utils is
                           ,modifier   => 'm');
    return l_caller_stack_line;
   end;
-
-
+
+  /**
+  * Change string into unicode to match xmlgen format _00<unicode>_
+  * https://docs.oracle.com/en/database/oracle/oracle-database/12.2/adxdb/generation-of-XML-data-from-relational-data.html#GUID-5BE09A7D-80D8-4734-B9AF-4A61F27FA9B2
+  * secion 8.2.1.1
+  */  
+  function char_to_xmlgen_unicode(a_character varchar2) return varchar2 is
+  begin
+    return '_x00'||rawtohex(utl_raw.cast_to_raw(a_character))||'_';
+  end;
+
+  /**
+  * Build valid XML column name as element names can contain letters, digits, hyphens, underscores, and periods
+  */  
+  function build_valid_xml_name(a_preprocessed_name varchar2) return varchar2 is
+    l_post_processed varchar2(4000);
+  begin
+    for i in (select regexp_substr( a_preprocessed_name ,'(.{1})', 1, level, null, 1 ) AS string_char,level level_no
+              from   dual connect by level <= regexp_count(a_preprocessed_name, '(.{1})'))
+    loop
+      if i.level_no = 1 and regexp_like(i.string_char,gc_invalid_first_xml_char) then
+        l_post_processed := l_post_processed || char_to_xmlgen_unicode(i.string_char);
+      elsif regexp_like(i.string_char,gc_invalid_xml_char) then
+        l_post_processed := l_post_processed || char_to_xmlgen_unicode(i.string_char);
+      else
+        l_post_processed := l_post_processed || i.string_char;
+      end if;
+    end loop;
+    return l_post_processed;  
+  end;
+
+  function get_valid_xml_name(a_name varchar2) return varchar2 is
+    l_valid_name varchar2(4000);
+  begin
+    if regexp_like(a_name,gc_full_valid_xml_name) then
+      l_valid_name := a_name;
+    else
+      l_valid_name := build_valid_xml_name(a_name);
+    end if;
+    return l_valid_name;
+  end;
 
 end ut_utils;
 /
diff --git a/source/core/ut_utils.pks b/source/core/ut_utils.pks
@@ -22,7 +22,7 @@ create or replace package ut_utils authid definer is
    */
 
   gc_version                 constant varchar2(50) := 'v3.1.7.2844-develop';
-
+    
   subtype t_executable_type      is varchar2(30);
   gc_before_all                  constant t_executable_type := 'beforeall';
   gc_before_each                 constant t_executable_type := 'beforeeach';
@@ -381,6 +381,11 @@ create or replace package ut_utils authid definer is
   * Remove given ORA error from stack
   */
   function remove_error_from_stack(a_error_stack varchar2, a_ora_code number) return varchar2;
-
+
+  /**
+  * Check if xml name is valid if not build a valid name
+  */
+  function get_valid_xml_name(a_name 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
@@ -80,13 +80,13 @@ create or replace package body ut_compound_data_helper is
   begin
     execute immediate q'[with
           expected_cols as (
-            select access_path exp_column_name,column_position exp_col_pos,
+            select display_path exp_column_name,column_position exp_col_pos,
                    replace(column_type_name,'VARCHAR2','CHAR') exp_col_type_compare, column_type_name exp_col_type
               from table(:a_expected)
               where parent_name is null and hierarchy_level = 1 and column_name is not null
           ),
           actual_cols as (
-            select access_path act_column_name,column_position act_col_pos,
+            select display_path act_column_name,column_position act_col_pos,
                    replace(column_type_name,'VARCHAR2','CHAR') act_col_type_compare, column_type_name act_col_type
               from table(:a_actual)
               where parent_name is null and hierarchy_level = 1 and column_name is not null

diff --git a/source/expectations/data_values/ut_cursor_column.tpb b/source/expectations/data_values/ut_cursor_column.tpb
@@ -13,17 +13,24 @@ create or replace type body ut_cursor_column as
       self.column_len       := a_col_max_len; --length of column
       self.column_name      := TRIM( BOTH '''' FROM a_col_name); --name of the column
       self.column_type_name := coalesce(a_col_type_name,a_col_type); --type name e.g. test_dummy_object or varchar2
-      self.access_path      := case when a_access_path is null then 
+      self.xml_valid_name   := ut_utils.get_valid_xml_name(self.column_name);
+      self.display_path     := case when a_access_path is null then 
                                  self.column_name 
                                else 
                                  a_access_path||'/'||self.column_name 
-                               end; --Access path used for incldue exclude eg/ TEST_DUMMY_OBJECT/VARCHAR2
-      self.xml_valid_name   := '"'||self.column_name||'"'; --User friendly column name
-      self.transformed_name := case when self.parent_name is null then 
-                                self.xml_valid_name
-                             else 
-                                '"'||ut_compound_data_helper.get_fixed_size_hash(self.parent_name||self.column_name)||'"'
-                             end; --when is nestd we need to hash name to make sure we dont exceed 30 char
+                               end; --Access path used for incldue exclude eg/ TEST_DUMMY_OBJECT/VARCHAR2       
+      self.access_path      := case when a_access_path is null then 
+                                 self.xml_valid_name 
+                               else 
+                                 a_access_path||'/'||self.xml_valid_name 
+                               end; --Access path used for incldue exclude eg/ TEST_DUMMY_OBJECT/VARCHAR2     
+      self.transformed_name := case when length(self.xml_valid_name) > 30 then
+                                 '"'||ut_compound_data_helper.get_fixed_size_hash(self.parent_name||self.xml_valid_name)||'"'
+                               when self.parent_name is null then 
+                                 '"'||self.xml_valid_name||'"'
+                               else 
+                                 '"'||ut_compound_data_helper.get_fixed_size_hash(self.parent_name||self.xml_valid_name)||'"'
+                               end; --when is nestd we need to hash name to make sure we dont exceed 30 char
       self.column_type      := a_col_type; --column type e.g. user_defined , varchar2
       self.column_schema    := a_col_schema_name; -- schema name
       self.is_sql_diffable  := case 

diff --git a/source/expectations/data_values/ut_cursor_column.tps b/source/expectations/data_values/ut_cursor_column.tps
@@ -17,12 +17,13 @@ create or replace type ut_cursor_column force authid current_user as object (
   */
    parent_name      varchar2(4000),
    access_path      varchar2(4000),
+   display_path     varchar2(4000),
    has_nested_col   number(1,0),
-   transformed_name varchar2(32),
+   transformed_name varchar2(2000),
    hierarchy_level  number,
    column_position  number,
-   xml_valid_name   varchar2(128),
-   column_name      varchar2(128),
+   xml_valid_name   varchar2(2000),
+   column_name      varchar2(2000),
    column_type      varchar2(128),
    column_type_name varchar2(128),
    column_schema    varchar2(128),

diff --git a/test/ut3_user/expectations/test_expectations_cursor.pkb b/test/ut3_user/expectations/test_expectations_cursor.pkb
@@ -2618,5 +2618,64 @@ Check the query and data for errors.';
     ut3.ut.reset_nls;
 
   end;
+
+  procedure colon_part_of_columnname is
+    type t_key_val_rec is record(
+    key varchar2(100),
+    value varchar2(100));
+
+    l_act     t_key_val_rec;
+    l_exp     t_key_val_rec;
+    l_act_cur sys_refcursor;
+    l_exp_cur sys_refcursor;
+  begin
+    l_act.key := 'NAME';
+    l_act.value := 'TEST';
+    l_exp.key := 'NAME';
+    l_exp.value := 'TEST';    
+
+   OPEN l_act_cur FOR SELECT l_act.key, l_act.value
+     FROM dual;
+
+   OPEN l_exp_cur FOR SELECT l_exp.key, l_exp.value
+     FROM dual;
+
+   ut3.ut.expect(l_act_cur).to_equal(l_exp_cur);
+   ut.expect(ut3_tester_helper.main_helper.get_failed_expectations_num).to_equal(0);
+
+  end;
+
+  procedure specialchar_part_of_colname is
+    l_act_cur sys_refcursor;
+    l_exp_cur sys_refcursor;
+  begin
+
+   OPEN l_act_cur FOR SELECT 1 as "$Test", 2 as "&Test"
+     FROM dual;
+
+   OPEN l_exp_cur FOR SELECT 1 as "$Test", 2 as "&Test"
+     FROM dual;
+
+   ut3.ut.expect(l_act_cur).to_equal(l_exp_cur);  
+   ut.expect(ut3_tester_helper.main_helper.get_failed_expectations_num).to_equal(0);
+
+  end;
+
+  procedure nonxmlchar_part_of_colname is
+    l_act_cur sys_refcursor;
+    l_exp_cur sys_refcursor;
+  begin
+
+   OPEN l_act_cur FOR SELECT 1 as "<Test>", 2 as "_Test", 3 as ".Test>"
+     FROM dual;
+
+   OPEN l_exp_cur FOR SELECT 1 as "<Test>", 2 as "_Test", 3 as ".Test>"
+     FROM dual;
+
+   ut3.ut.expect(l_act_cur).to_equal(l_exp_cur); 
+   ut.expect(ut3_tester_helper.main_helper.get_failed_expectations_num).to_equal(0);
+
+  end;  
+
 end;
 /
diff --git a/test/ut3_user/expectations/test_expectations_cursor.pks b/test/ut3_user/expectations/test_expectations_cursor.pks
@@ -408,6 +408,15 @@ create or replace package test_expectations_cursor is
 
   --%test(Check that cursor correctly handles no length dataypes)
   procedure no_length_datatypes;
+
+  --%test(Check that colon is converted properly fix #902)
+  procedure colon_part_of_columnname;
+
+  --%test(Check that column name accept special characters fix #902)
+  procedure specialchar_part_of_colname;  
+
+  --%test(Check that column name accept non xml characters fix #902)
+  procedure nonxmlchar_part_of_colname;
 
 end;
 /