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






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.








32.15.CAST
32.15.1.The CAST Function
32.15.2.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.
32.15.3.Transform a collection into a table and reference it in a SQL query.
32.15.4.Cast value to custom type
32.15.5.Use view to cast records in result set to a type