The CAST function may also be used with the MULTISET function to perform DML operations on VARRAYs. MULTISET is the "reverse" of CAST in that MULTISET converts a nonobject set of data to an object set. Suppose we create a new table of names:
SQL>
SQL> CREATE OR REPLACE TYPE mem_type IS VARRAY(10) of VARCHAR2(15)
2 /
Type created.
SQL>
SQL> CREATE TABLE club (Name VARCHAR2(10),
2 Address VARCHAR2(20),
3 City VARCHAR2(20),
4 Phone VARCHAR2(8),
5 Members mem_type)
6 /
Table created.
SQL>
SQL> INSERT INTO club VALUES ('AL','111 First St.','Mobile',
2 '222-2222', mem_type('Brenda','Richard'));
1 row created.
SQL>
SQL> INSERT INTO club VALUES ('FL','222 Second St.','Orlando',
2 '333-3333', mem_type('Gen','John','Steph','JJ'));
1 row created.
SQL>
SQL> SELECT COLUMN_VALUE FROM
2 THE(SELECT CAST(c.members as mem_type)
3 FROM club c
4 WHERE c.name = 'FL');
COLUMN_VALUE
---------------
Gen
John
Steph
JJ
SQL>
SQL> CREATE TABLE newnames (n varchar2(20))
2 /
Table created.
SQL> INSERT INTO newnames VALUES ('Beryl')
2 /
1 row created.
SQL> INSERT INTO newnames VALUES ('Fred')
2 /
1 row created.
SQL> SELECT *
2 FROM newnames
3 /
N
--------------------
Beryl
Fred
SQL>
SQL> INSERT INTO club VALUES ('VA',null,null,null,null)
2 /
1 row created.
SQL> UPDATE club SET members =
2 CAST(MULTISET(SELECT n FROM newnames) as mem_type)
3 WHERE name = 'VA'
4 /
1 row updated.
SQL>
SQL> select * from club;
NAME ADDRESS CITY PHONE
---------- -------------------------------------------------- -------------------- --------
MEMBERS
-------------------------------------------------------------------------------------------
AL 111 First St. Mobile 222-2222
MEM_TYPE('Brenda', 'Richard')
FL 222 Second St. Orlando 333-3333
MEM_TYPE('Gen', 'John', 'Steph', 'JJ')
VA
MEM_TYPE('Beryl', 'Fred')
SQL>
SQL> INSERT INTO club VALUES('MD',null, null,null,
2 CAST(MULTISET(SELECT * FROM newnames) as mem_type))
3 /
1 row created.
SQL>
SQL> select * from club;
NAME ADDRESS CITY PHONE
---------- -------------------------------------------------- -------------------- --------
MEMBERS
-------------------------------------------------------------------------------------------
AL 111 First St. Mobile 222-2222
MEM_TYPE('Brenda', 'Richard')
FL 222 Second St. Orlando 333-3333
MEM_TYPE('Gen', 'John', 'Steph', 'JJ')
VA
MEM_TYPE('Beryl', 'Fred')
MD
MEM_TYPE('Beryl', 'Fred')
SQL>
SQL> drop table newnames;
Table dropped.
SQL>
SQL> drop table club;
Table dropped.
SQL> drop type mem_type;
Type dropped.