The CAST function converts an object type (such as a VARRAY) into a common type that can be queried. Oracle 10g automatically converts the VARRAY without the CAST. : CAST « Object Oriented « Oracle PL/SQL Tutorial

1. Oracle PL/SQL Tutorial
2. Object Oriented
3. CAST

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>
SQL> CREATE OR REPLACE TYPE mem_type IS VARRAY(10) of VARCHAR2(15)
  2  /

Type created.

SQL>
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>
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>
SQL>
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>
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>
SQL> drop table newnames;

Table dropped.

SQL>
SQL> drop table club;

Table dropped.

SQL> drop type mem_type;

Type dropped.