Adding test for double nested objects

utPLSQL · lwasylow · Jan 29, 2022 · Jan 26, 2022 · Jan 26, 2022 · Jan 26, 2022
commit e0ac97ca4ebb3cdb391ebdf09084413961322657
diff --git a/source/expectations/data_values/ut_compound_data_helper.pkb b/source/expectations/data_values/ut_compound_data_helper.pkb
@@ -144,9 +144,7 @@ create or replace package body ut_compound_data_helper is
         l_index := a_pk_table.next(l_index);
       end loop;
     end if;  
-    if a_data_info.column_type in ('OBJECT') then    
-      null;  
-    elsif not(l_exists) then  
+    if not(l_exists) then  
       l_sql_stmt := ' (decode(a.'||a_data_info.transformed_name||','||' e.'||a_data_info.transformed_name||',1,0) = 0)';
     end if; 
     return l_sql_stmt;
@@ -163,9 +161,7 @@ create or replace package body ut_compound_data_helper is
     if l_pk_tab.count <> 0 then
     l_index:= l_pk_tab.first;
     loop
-      if a_data_info.column_type in ('OBJECT') then    
-          null;
-      elsif l_pk_tab(l_index) in (a_data_info.access_path, a_data_info.parent_name)  then
+      if l_pk_tab(l_index) in (a_data_info.access_path, a_data_info.parent_name)  then
         --When then table is nested and join is on whole table
         l_sql_stmt := l_sql_stmt ||' a.'||a_data_info.transformed_name||q'[ = ]'||' e.'||a_data_info.transformed_name;
       end if;
@@ -191,21 +187,15 @@ create or replace package body ut_compound_data_helper is
     if a_pk_table is not empty then
       l_index:= a_pk_table.first;
       loop
-        if a_data_info.column_type in ('OBJECT') then    
-          null;
-        elsif a_pk_table(l_index) in (a_data_info.access_path, a_data_info.parent_name) then
+        if a_pk_table(l_index) in (a_data_info.access_path, a_data_info.parent_name) then
           --When then table is nested and join is on whole table
           l_sql_stmt := l_sql_stmt ||a_alias||a_data_info.transformed_name;
         end if;        
         exit when (a_data_info.access_path = a_pk_table(l_index)) or l_index = a_pk_table.count;
         l_index := a_pk_table.next(l_index);
       end loop;
     else
-      if a_data_info.column_type in ('OBJECT') then    
-        null;   
-      else 
-       l_sql_stmt := a_alias||a_data_info.transformed_name;
-      end if;
+      l_sql_stmt := a_alias||a_data_info.transformed_name;
     end if;
     return l_sql_stmt; 
   end;   
@@ -216,9 +206,7 @@ create or replace package body ut_compound_data_helper is
     l_alias varchar2(10) := a_alias;
     l_col_syntax varchar2(4000);
   begin
-    if a_data_info.column_type in ('OBJECT') then    
-      null;
-    elsif a_data_info.is_sql_diffable = 0 then 
+    if a_data_info.is_sql_diffable = 0 then 
       l_col_syntax :=  'ut_utils.get_hash('||l_alias||a_data_info.transformed_name||'.getClobVal()) as '||a_data_info.transformed_name ;
     elsif a_data_info.is_sql_diffable = 1  and a_data_info.column_type = 'DATE' then
       l_col_syntax :=  'to_date('||l_alias||a_data_info.transformed_name||') as '|| a_data_info.transformed_name;
@@ -250,20 +238,14 @@ create or replace package body ut_compound_data_helper is
       --We cannot use a precision and scale as dbms_sql.describe_columns3 return precision 0 for dual table
       -- there is also no need for that as we not process data but only read and compare as they are stored
       l_col_type := a_data_info.column_type;
-    elsif a_data_info.column_type in ('OBJECT') then    
-      null;
     else 
       l_col_type := a_data_info.column_type
         ||case when a_data_info.column_len is not null
           then '('||a_data_info.column_len||')'
           else null
           end;
     end if;
-    if a_data_info.column_type in ('OBJECT') then    
-      return null;
-    else
-      return  a_data_info.transformed_name||' '||l_col_type||q'[ PATH ']'||a_data_info.access_path||q'[']';
-    end if;
+    return  a_data_info.transformed_name||' '||l_col_type||q'[ PATH ']'||a_data_info.access_path||q'[']';
   end;
 
   procedure gen_sql_pieces_out_of_cursor(
@@ -296,7 +278,7 @@ create or replace package body ut_compound_data_helper is
   begin
     if a_data_info is not empty then
       for i in 1..a_data_info.count loop
-        if a_data_info(i).has_nested_col = 0 then
+        if a_data_info(i).has_nested_col = 0 and a_data_info(i).column_type <> 'OBJECT' then
           --Get XMLTABLE column list
           add_element_to_list(l_xmltab_list,generate_xmltab_stmt(a_data_info(i)));
           --Get Select statment list of columns

diff --git a/test/install_ut3_tester_helper.sql b/test/install_ut3_tester_helper.sql
@@ -7,6 +7,7 @@ alter session set plsql_optimize_level=0;
 @@ut3_tester_helper/test_dummy_object.tps
 @@ut3_tester_helper/other_dummy_object.tps
 @@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_tab_varchar2.tps
 @@ut3_tester_helper/test_tab_varray.tps

diff --git a/test/ut3_tester_helper/test_dummy_double_nested_object.tps b/test/ut3_tester_helper/test_dummy_double_nested_object.tps
@@ -0,0 +1,15 @@
+declare
+  l_exists integer;
+begin
+  select count(1) into l_exists from user_types where type_name = 'TEST_DUMMY_DOUBLE_NESTED_OBJECT';
+  if l_exists > 0 then
+    execute immediate 'drop type test_dummy_double_nested_object force';
+  end if;
+end;
+/
+
+create or replace type test_dummy_double_nested_object as object (
+  first_double_nested_obj test_dummy_nested_object,
+  "Value" varchar2(30)
+)
+/
diff --git a/test/ut3_user/expectations/test_expectation_anydata.pkb b/test/ut3_user/expectations/test_expectation_anydata.pkb
@@ -1000,16 +1000,35 @@ Rows: [ 60 differences, showing first 20 ]
     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, 'C', '0') ));
     --Act
-    l_expected_message := q'[%Actual: ut3_develop.some_item was expected to equal: ut3_develop.some_item
+    l_expected_message := q'[%Actual: ut3_tester_helper.test_dummy_nested_object was expected to equal: ut3_tester_helper.test_dummy_nested_object
 %Diff:
 %Rows: [ 1 differences ]
-%Row No. 1 - Actual:   <SEC_NESTED_OBJ><ID>1</ID><name>B</name><Value>0</Value></SEC_NESTED_OBJ>
-%Row No. 1 - Expected: <SEC_NESTED_OBJ><ID>1</ID><name>C</name><Value>0</Value></SEC_NESTED_OBJ>]';
+%Row No. 1 - Actual:   <SEC_NESTED_OBJ><ID>1</ID><name>C</name><Value>0</Value></SEC_NESTED_OBJ>
+%Row No. 1 - Expected: <SEC_NESTED_OBJ><ID>1</ID><name>B</name><Value>0</Value></SEC_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 failure_double_nested_objects is
+    l_actual_message   varchar2(32767);
+    l_expected_message varchar2(32767);	  
+  begin
+  --Arrange
+    g_test_expected := anydata.convertObject( ut3_tester_helper.test_dummy_double_nested_object(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_object(ut3_tester_helper.test_dummy_nested_object(ut3_tester_helper.test_dummy_object(1, 'A', '0'),ut3_tester_helper.test_dummy_object(1, 'C', '0') ),'Test'));
+    --Act
+    l_expected_message := q'[%Actual: ut3_tester_helper.test_dummy_double_nested_object was expected to equal: ut3_tester_helper.test_dummy_double_nested_object
+%Diff:
+%Rows: [ 1 differences ]
+%Row No. 1 - Actual:   <FIRST_DOUBLE_NESTED_OBJ><FIRST_NESTED_OBJ><ID>1</ID><name>A</name><Value>0</Value></FIRST_NESTED_OBJ><SEC_NESTED_OBJ><ID>1</ID><name>C</name><Value>0</Value></SEC_NESTED_OBJ></FIRST_DOUBLE_NESTED_OBJ>
+%Row No. 1 - Expected: <FIRST_DOUBLE_NESTED_OBJ><FIRST_NESTED_OBJ><ID>1</ID><name>A</name><Value>0</Value></FIRST_NESTED_OBJ><SEC_NESTED_OBJ><ID>1</ID><name>B</name><Value>0</Value></SEC_NESTED_OBJ></FIRST_DOUBLE_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;  
+
 end;
 /
diff --git a/test/ut3_user/expectations/test_expectation_anydata.pks b/test/ut3_user/expectations/test_expectation_anydata.pks
@@ -206,6 +206,9 @@ create or replace package test_expectation_anydata is
 
   --%test ( Failure of comparing nesting objects )
   procedure failure_nesting_objects;
+
+  --%test ( Failure of comparing double nested objects ) 
+  procedure failure_double_nested_objects;
 
 end;
 /