The CAST Function : CAST « Object Oriented « Oracle PL/SQL Tutorial

The 'THE' function is one way to get individual members from the VARRAY.

The CAST function is used to convert collection types to ordinary, common types in Oracle. CAST may be used in a SELECT to explicitly define that a collection type is being converted:

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>
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