utPLSQL · lwasylow · Jan 29, 2022 · Jan 26, 2022 · Jan 26, 2022 · Jan 26, 2022
diff --git a/source/core/ut_utils.pkb b/source/core/ut_utils.pkb
@@ -882,12 +882,14 @@ create or replace package body ut_utils is
 
   function get_hash(a_data raw, a_hash_type binary_integer := dbms_crypto.hash_sh1) return t_hash is
   begin
-    return dbms_crypto.hash(a_data, a_hash_type);
+    --We cannot run hash on null
+    return case when a_data is null then null else dbms_crypto.hash(a_data, a_hash_type) end;
   end;
 
   function get_hash(a_data clob, a_hash_type binary_integer := dbms_crypto.hash_sh1) return t_hash is
   begin
-    return dbms_crypto.hash(a_data, a_hash_type);
+    --We cannot run hash on null
+    return case when a_data is null then null else dbms_crypto.hash(a_data, a_hash_type) end;
   end;
 
   function qualified_sql_name(a_name varchar2) return varchar2 is

diff --git a/source/expectations/data_values/ut_compound_data_helper.pkb b/source/expectations/data_values/ut_compound_data_helper.pkb
@@ -278,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
@@ -398,8 +398,12 @@ create or replace package body ut_compound_data_helper is
     l_column_list ut_varchar2_list := ut_varchar2_list();
   begin
     for i in 1..a_cursor_info.count loop
-      l_column_list.extend;
-      l_column_list(l_column_list.last) := a_cursor_info(i).access_path;
+      --This avoids extracting single columns from nested objects.
+      --as we can go down to any level but we will lose visibility of parent.
+      if a_cursor_info(i).hierarchy_level = 1 then
+        l_column_list.extend;
+        l_column_list(l_column_list.last) := a_cursor_info(i).access_path;
+      end if;
     end loop;
     return l_column_list;
   end;

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,15 +28,16 @@ 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.
       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)||'"'
+                                 '"'||ut_compound_data_helper.get_fixed_size_hash(self.access_path)||'"'
                                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)||'"'
+                                 '"'||ut_compound_data_helper.get_fixed_size_hash(self.access_path)||'"'
                                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

diff --git a/test/install_ut3_tester_helper.sql b/test/install_ut3_tester_helper.sql
@@ -6,9 +6,15 @@ alter session set plsql_optimize_level=0;
 --Install ut3_tester_helper
 @@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_dummy_nested_object_list.tps
+@@ut3_tester_helper/test_dummy_double_nested_list.tps
+@@ut3_tester_helper/test_dummy_dble_nest_lst_obj.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_dble_nest_lst_obj.tps b/test/ut3_tester_helper/test_dummy_dble_nest_lst_obj.tps
@@ -0,0 +1,17 @@
+declare
+  l_exists integer;
+begin
+  select count(1) into l_exists from user_types where type_name = 'TEST_DUMMY_DBLE_NEST_LST_OBJ';
+  if l_exists > 0 then
+    execute immediate 'drop type test_dummy_dble_nest_lst_obj force';
+  end if;
+end;
+/
+
+CREATE TYPE test_dummy_dble_nest_lst_obj AS OBJECT 
+( 
+  some_number_id NUMBER, 
+  some_name VARCHAR2 (25), 
+  dummy_list test_dummy_double_nested_list 
+);
+/
diff --git a/test/ut3_tester_helper/test_dummy_double_nested_list.tps b/test/ut3_tester_helper/test_dummy_double_nested_list.tps
@@ -0,0 +1,13 @@
+declare
+  l_exists integer;
+begin
+  select count(1) into l_exists from user_types where type_name = 'TEST_DUMMY_DOUBLE_NESTED_LIST';
+  if l_exists > 0 then
+    execute immediate 'drop type test_dummy_double_nested_list force';
+  end if;
+end;
+/
+
+CREATE TYPE test_dummy_double_nested_list AS
+    TABLE OF test_dummy_nested_object_list;
+/
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_OBJ';
+  if l_exists > 0 then
+    execute immediate 'drop type test_dummy_double_nested_obj force';
+  end if;
+end;
+/
+
+create or replace type test_dummy_double_nested_obj as object (
+  first_double_nested_obj test_dummy_nested_object,
+  "Value" varchar2(30)
+)
+/
diff --git a/test/ut3_tester_helper/test_dummy_nested_object.tps b/test/ut3_tester_helper/test_dummy_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_NESTED_OBJECT';
+  if l_exists > 0 then
+    execute immediate 'drop type test_dummy_nested_object force';
+  end if;
+end;
+/
+
+create or replace type test_dummy_nested_object as object (
+  first_nested_obj test_dummy_object,
+  sec_nested_obj test_dummy_object
+)
+/
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,15 @@
+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,
+  somename varchar2(50)
+)
+/
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
+)
+/