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

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

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.