Multiset Union ALL operation on NESTED tables built on OBJECT Types

This blog is to share some difficulties when we try to use MULTISET UNION ALL command on NESTED TABLES
built on OBJECT TYPES.In our example the nested table test_obj_array is built on a type called test_obj.

I will then mention a small workaround which is not perfect but workable by creating a map member function

SQL> select col1 from junk_tab;

COL1
--------------------------------------------------------------------------------
gautham
siddhu
surya

SQL> create or replace type test_obj as object(col1 varchar2(100),col2 varchar2(100), col3 varchar2(
100));
  2  /

Type created.

SQL> create type test_obj_array as table of test_obj;
  2  /

Type created.



SQL>  select cast(multiset(select test_obj(col1,col1,col1) from junk_tab) as test_obj_array) from du
al;

CAST(MULTISET(SELECTTEST_OBJ(COL1,COL1,COL1)FROMJUNK_TAB)ASTEST_OBJ_ARRAY)(COL1,
--------------------------------------------------------------------------------
TEST_OBJ_ARRAY(TEST_OBJ('gautham', 'gautham', 'gautham'), TEST_OBJ('siddhu', 'si
ddhu', 'siddhu'), TEST_OBJ('surya', 'surya', 'surya'))

It gets more complicated when we use MULTISET UNION ALL command when are dealing with NESTED TABLES built on OBJECT TYPES

SQL> DECLARE
  2   x test_obj_array := test_obj_array();
  3   y test_obj_array := test_obj_array();
  4   z test_obj_array := test_obj_array();
  5  BEGIN
  6     SELECT cast( multiset(select test_obj(col1,col1,col1) from junk_tab) as test_obj_array )
  7     INTO x from dual;
  8  y:=x;
  9  z:=x multiset union all y;
 10 
 11  END;
 12  /
z:=x multiset union all y;
*
ERROR at line 9:
ORA-06550: line 9, column 1:
PLS-00801: internal error [*** ASSERT at file pdw4.c, line 2079; Type
0x87ffffffbf3eb9a0 has no MAP method.; _anon__c0000000381e5be8__AB[9, 1]]

SQL> drop type test_obj_array;

Type dropped.

SQL> drop type test_obj;

Type dropped.

create or replace type test_obj as object(col1 varchar2(100),col2 varchar2(100), col3 varchar2(
100),map member function test_function return varchar2);
/

create type test_obj_array as table of test_obj;
/


SQL> create or replace type body test_obj
  2  as
  3  map member function test_function return varchar2 is
  4  begin
  5   return col1||col2||col3;
  6  end;
  7  end;
  8  /

Type body created.


SQL>  select cast(multiset(select test_obj(col1,col1,col1) from junk_tab) as test_obj_array) from du
al;

CAST(MULTISET(SELECTTEST_OBJ(COL1,COL1,COL1)FROMJUNK_TAB)ASTEST_OBJ_ARRAY)(COL1,
--------------------------------------------------------------------------------
TEST_OBJ_ARRAY(TEST_OBJ('gautham', 'gautham', 'gautham'), TEST_OBJ('siddhu', 'si
ddhu', 'siddhu'), TEST_OBJ('surya', 'surya', 'surya'))

SQL> DECLARE
  2   x test_obj_array := test_obj_array();
  3   y test_obj_array := test_obj_array();
  4   z test_obj_array := test_obj_array();
  5  BEGIN
  6     SELECT cast( multiset(select test_obj(col1,col1,col1) from junk_tab) as test_obj_array )
  7     INTO x from dual;
  8  y:=x;
  9  z:=x multiset union all y;
 10  for i in 1 .. z.count() loop dbms_output.put_line(z(i).col1); end loop;
 11 
 12  END;
 13  /
gautham
siddhu
surya
gautham
siddhu
surya

PL/SQL procedure successfully completed.

SQL>