Cast/Multiset/Multiset Union Operations using Object Types

 In this blog,I have given a small example of how to transfer the contents of a table column into a nested table and then print the contents out using CAST /MULTISET operation.

Also checkout the MULTISET UNION command I have used to merge 2 nested tables.

SQL> create table junk_tab(col1 varchar2(100));

Table created.

SQL> insert into junk_tab values('&1');
Enter value for 1: gautham
old   1: insert into junk_tab values('&1')
new   1: insert into junk_tab values('gautham')

1 row created.

SQL> /
Enter value for 1: siddhu
old   1: insert into junk_tab values('&1')
new   1: insert into junk_tab values('siddhu')

1 row created.

SQL> /
Enter value for 1: surya
old   1: insert into junk_tab values('&1')
new   1: insert into junk_tab values('surya')

1 row created.

SQL> select * from junk_tab;

COL1
--------------------------------------------------------------------------------
gautham
siddhu
surya
SQL> create type string_tab as table of varchar2(100);
  2  /

Type created.

SQL> desc string_tab
 string_tab TABLE OF VARCHAR2(100 CHAR)
SQL> select cast(multiset(select col1 from junk_tab) as string_tab) from dual;

CAST(MULTISET(SELECTCOL1FROMJUNK_TAB)ASSTRING_TAB)
--------------------------------------------------------------------------------
STRING_TAB('gautham', 'siddhu', 'surya')


SQL> declare
  2     x string_tab := string_tab();
  3      y string_tab := string_tab();
  4 
  5      z string_tab := string_tab();
  6      begin
  7      select cast(multiset(select col1 from junk_tab) as string_tab) into x from dual;
  8      y := x;
  9 
 10      z := x multiset union all y;
 11  for i in 1 .. z.count()
 12  loop
 13  dbms_output.put_line(z(i));
 14 
 15  end loop;
 16  end;
 17  /
gautham
siddhu
surya
gautham
siddhu
surya

PL/SQL procedure successfully completed.

One more way to use multiset union all --
SQL> declare
  2     x string_tab := string_tab('gautham');
  3      y string_tab := string_tab('siddhu','surya');
  4 
  5      z string_tab := string_tab();
  6      begin
  7     z:=x multiset union all y;
  8  for i in 1 .. z.count()
  9  loop
 10  dbms_output.put_line(z(i));
 11 
 12  end loop;
 13  end;
 14  /
gautham
siddhu
surya

PL/SQL procedure successfully completed.

SQL>