Fixing issue when a collection nested inside object was failing due t…

…o fact that there was no name for collection columns as part of cursor only data type. Added extra tests.
utPLSQL · lwasylow · Jan 29, 2022 · Jan 26, 2022 · Jan 26, 2022 · Jan 26, 2022
commit 14b0b8d7953c62fe4ca94d78d830e20c41c82c13
diff --git a/source/expectations/data_values/ut_cursor_column.tpb b/source/expectations/data_values/ut_cursor_column.tpb
@@ -14,8 +14,11 @@ create or replace type body ut_cursor_column as
       self.column_len       := a_col_max_len; --length of column
       self.column_precision := a_col_precision;
       self.column_scale     := a_col_scale;
-      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.column_name      := case when a_col_name is null and a_collection = 1 then 
+                                 self.column_type_name 
+                               else TRIM( BOTH '''' FROM a_col_name) 
+                               end;  --name of the column, however in nested object for collection name is not defined in cursor.
       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 
@@ -25,7 +28,7 @@ create or replace type body ut_cursor_column as
       self.access_path      := case when a_access_path is null then 
                                  self.xml_valid_name 
                                else 
-                                 a_access_path||'/'||self.xml_valid_name 
+                                 a_access_path||'/'||self.xml_valid_name
                                end; --Access path used for XMLTABLE query   
       self.filter_path      := '/'||self.access_path; --Filter path will differ from access path in anydata type
       --Transformed name needs to be build on full access path to avoid ambiguity when there is 3 or more levels of nesting.

diff --git a/test/install_ut3_tester_helper.sql b/test/install_ut3_tester_helper.sql
@@ -9,8 +9,10 @@ alter session set plsql_optimize_level=0;
 @@ut3_tester_helper/test_dummy_nested_object.tps
 @@ut3_tester_helper/test_dummy_double_nested_object.tps
 @@ut3_tester_helper/test_dummy_object_list.tps
+@@ut3_tester_helper/test_dummy_nested_object_list.tps
 @@ut3_tester_helper/test_tab_varchar2.tps
 @@ut3_tester_helper/test_tab_varray.tps
+@@ut3_tester_helper/test_nested_tab_varray.tps
 @@ut3_tester_helper/test_dummy_number.tps
 @@ut3_tester_helper/ut_test_table.sql
 @@ut3_tester_helper/test_event_object.tps

diff --git a/test/ut3_tester_helper/test_dummy_nested_object_list.tps b/test/ut3_tester_helper/test_dummy_nested_object_list.tps
@@ -0,0 +1,14 @@
+declare
+  l_exists integer;
+begin
+  select count(1) into l_exists from user_types where type_name = 'TEST_DUMMY_NESTED_OBJECT_LIST';
+  if l_exists > 0 then
+    execute immediate 'drop type test_dummy_nested_object_list force';
+  end if;
+end;
+/
+
+create or replace type test_dummy_nested_object_list as object (
+  first_nested_obj test_dummy_object_list
+)
+/
diff --git a/test/ut3_tester_helper/test_dummy_object_list.tps b/test/ut3_tester_helper/test_dummy_object_list.tps
@@ -1,2 +1,12 @@
+declare
+  l_exists integer;
+begin
+  select count(1) into l_exists from user_types where type_name = 'TEST_DUMMY_OBJECT_LIST';
+  if l_exists > 0 then
+    execute immediate 'drop type test_dummy_object_list force';
+  end if;
+end;
+/
+
 create or replace type test_dummy_object_list as table of test_dummy_object
 /
diff --git a/test/ut3_tester_helper/test_nested_tab_varray.tps b/test/ut3_tester_helper/test_nested_tab_varray.tps
@@ -0,0 +1,14 @@
+declare
+  l_exists integer;
+begin
+  select count(1) into l_exists from user_types where type_name = 'TEST_NESTED_TAB_VARRAY';
+  if l_exists > 0 then
+    execute immediate 'drop type test_nested_tab_varray force';
+  end if;
+end;
+/
+
+create or replace type test_nested_tab_varray as object (
+  n_varray t_varray
+)
+/
diff --git a/test/ut3_user/expectations/test_expectation_anydata.pkb b/test/ut3_user/expectations/test_expectation_anydata.pkb
@@ -1029,6 +1029,105 @@ Rows: [ 60 differences, showing first 20 ]
     --Assert
     ut.expect(l_actual_message).to_be_like(l_expected_message);   
   end;  
+
+  procedure success_nesting_objects is 
+  begin
+  --Arrange
+    g_test_expected := anydata.convertObject( ut3_tester_helper.test_dummy_nested_object(ut3_tester_helper.test_dummy_object(1, 'A', '0'),ut3_tester_helper.test_dummy_object(1, 'B', '0') ));
+    g_test_actual   := anydata.convertObject( ut3_tester_helper.test_dummy_nested_object(ut3_tester_helper.test_dummy_object(1, 'A', '0'),ut3_tester_helper.test_dummy_object(1, 'B', '0') ));
+   --Act
+   ut3_develop.ut.expect( g_test_actual ).to_equal( g_test_expected );
+   ut.expect(ut3_tester_helper.main_helper.get_failed_expectations_num).to_equal(0);
+  end;
 
+  procedure success_double_nested_objects is
+  begin
+  --Arrange
+    g_test_expected := anydata.convertObject( ut3_tester_helper.test_dummy_double_nested_obj(ut3_tester_helper.test_dummy_nested_object(ut3_tester_helper.test_dummy_object(1, 'A', '0'),ut3_tester_helper.test_dummy_object(1, 'B', '0') ),'Test'));
+    g_test_actual   := anydata.convertObject( ut3_tester_helper.test_dummy_double_nested_obj(ut3_tester_helper.test_dummy_nested_object(ut3_tester_helper.test_dummy_object(1, 'A', '0'),ut3_tester_helper.test_dummy_object(1, 'B', '0') ),'Test'));
+   --Act
+   ut3_develop.ut.expect( g_test_actual ).to_equal( g_test_expected );
+   ut.expect(ut3_tester_helper.main_helper.get_failed_expectations_num).to_equal(0);
+  end;  
+
+  procedure failure_nested_object_list is
+    l_actual_message   varchar2(32767);
+    l_expected_message varchar2(32767);	  
+    l_actual           ut3_tester_helper.test_dummy_object_list;
+    l_expected         ut3_tester_helper.test_dummy_object_list;
+  begin
+    --Arrange
+    select ut3_tester_helper.test_dummy_object( rownum + 1, 'Something '||rownum, rownum)
+      bulk collect into l_actual
+      from dual connect by level <=2
+	  order by rownum desc;
+    select ut3_tester_helper.test_dummy_object( rownum, 'Something '||rownum, rownum)
+      bulk collect into l_expected
+      from dual connect by level <=2
+     order by rownum desc;
+  --Arrange
+    g_test_expected := anydata.convertObject( ut3_tester_helper.test_dummy_nested_object_list(l_actual));
+    g_test_actual   := anydata.convertObject( ut3_tester_helper.test_dummy_nested_object_list(l_expected));
+    --Act
+    l_expected_message := q'[%Actual: ut3_tester_helper.test_dummy_nested_object_list was expected to equal: ut3_tester_helper.test_dummy_nested_object_list
+%Diff:
+%Rows: [ 1 differences ]
+%Row No. 1 - Actual:   <FIRST_NESTED_OBJ><TEST_DUMMY_OBJECT><ID>2</ID><name>Something 2</name><Value>2</Value></TEST_DUMMY_OBJECT><TEST_DUMMY_OBJECT><ID>1</ID><name>Something 1</name><Value>1</Value></TEST_DUMMY_OBJECT></FIRST_NESTED_OBJ>
+%Row No. 1 - Expected: <FIRST_NESTED_OBJ><TEST_DUMMY_OBJECT><ID>3</ID><name>Something 2</name><Value>2</Value></TEST_DUMMY_OBJECT><TEST_DUMMY_OBJECT><ID>2</ID><name>Something 1</name><Value>1</Value></TEST_DUMMY_OBJECT></FIRST_NESTED_OBJ>]';
+    ut3_develop.ut.expect(g_test_actual).to_equal(g_test_expected);
+    l_actual_message := ut3_tester_helper.main_helper.get_failed_expectations(1);
+    --Assert
+    ut.expect(l_actual_message).to_be_like(l_expected_message);   
+  end;
+
+  procedure success_nested_object_list is
+    l_actual           ut3_tester_helper.test_dummy_object_list;
+    l_expected         ut3_tester_helper.test_dummy_object_list;
+  begin
+    --Arrange
+    select ut3_tester_helper.test_dummy_object( rownum , 'Something '||rownum, rownum)
+      bulk collect into l_actual
+      from dual connect by level <=2
+      order by rownum desc;
+    select ut3_tester_helper.test_dummy_object( rownum, 'Something '||rownum, rownum)
+      bulk collect into l_expected
+      from dual connect by level <=2
+     order by rownum desc;
+    --Arrange
+    g_test_expected := anydata.convertObject( ut3_tester_helper.test_dummy_nested_object_list(l_actual));
+    g_test_actual   := anydata.convertObject( ut3_tester_helper.test_dummy_nested_object_list(l_expected));
+    --Act
+    ut3_develop.ut.expect( g_test_actual ).to_equal( g_test_expected );
+    ut.expect(ut3_tester_helper.main_helper.get_failed_expectations_num).to_equal(0);
+  end;
+
+ procedure nested_varray_same_data is
+  begin
+    --Arrange
+    g_test_expected := anydata.convertObject( ut3_tester_helper.test_nested_tab_varray(ut3_tester_helper.t_varray(1)) );
+    g_test_actual := anydata.convertObject( ut3_tester_helper.test_nested_tab_varray(ut3_tester_helper.t_varray(1)) );
+    --Act
+    ut3_develop.ut.expect( g_test_actual ).to_equal( g_test_expected );
+    ut.expect(ut3_tester_helper.main_helper.get_failed_expectations_num).to_equal(0);
+  end;
+
+  procedure nested_varray_diff_data is
+    l_actual_message   varchar2(32767);
+    l_expected_message varchar2(32767);
+  begin
+    --Arrange
+    g_test_expected := anydata.convertObject( ut3_tester_helper.test_nested_tab_varray(ut3_tester_helper.t_varray(1)) );
+    g_test_actual   := anydata.convertObject( ut3_tester_helper.test_nested_tab_varray(ut3_tester_helper.t_varray(2)) );
+    --Act
+    ut3_develop.ut.expect( g_test_actual ).to_equal( g_test_expected );
+    l_expected_message := q'[%Actual: ut3_tester_helper.test_nested_tab_varray was expected to equal: ut3_tester_helper.test_nested_tab_varray
+%Diff:
+%Rows: [ 1 differences ]
+%Row No. 1 - Actual:   <N_VARRAY><NUMBER>2</NUMBER></N_VARRAY>
+%Row No. 1 - Expected: <N_VARRAY><NUMBER>1</NUMBER></N_VARRAY>]';
+    l_actual_message := ut3_tester_helper.main_helper.get_failed_expectations(1);
+    --Assert
+    ut.expect(l_actual_message).to_be_like(l_expected_message);
+  end;  
 end;
 /
diff --git a/test/ut3_user/expectations/test_expectation_anydata.pks b/test/ut3_user/expectations/test_expectation_anydata.pks
@@ -204,11 +204,28 @@ create or replace package test_expectation_anydata is
   --%test ( Empty Array not equal array with space )
   procedure arr_empty_nqua_arr_e_unord;  
 
-  --%test ( Failure of comparing nesting objects )
+  --%test ( Failure of comparing nested objects )
   procedure failure_nesting_objects;
 
   --%test ( Failure of comparing double nested objects ) 
   procedure failure_double_nested_objects;
 
+  --%test ( Success of comparing nested objects )
+  procedure success_nesting_objects;
+
+  --%test ( Success of comparing double nested objects ) 
+  procedure success_double_nested_objects;  
+
+  --%test ( Failure of comparing nested object list )
+  procedure failure_nested_object_list;
+
+  --%test ( Success of comparing nested object list )
+  procedure success_nested_object_list;
+
+  --%test(Nested VARRAYS with same data)
+  procedure nested_varray_same_data;
+
+  --%test(Nested VARRAYS with different data)
+  procedure nested_varray_diff_data;  
 end;
 /