Oracle PL/SQL Tutorial/Object Oriented/THE

The "THE" function generates a virtual table, which is displayed using the VALUE function for the elements. Using the COLUMN_VALUE function instead of the VALUE function will also work:

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> SELECT COLUMN_VALUE val FROM
  2  THE(SELECT c.members FROM club c
  3  WHERE c.name = "FL") x
  4  WHERE COLUMN_VALUE IS NOT NULL;
VAL
---------------
Gen
John
Steph
JJ
SQL>
SQL> drop table club;
Table dropped.
SQL> drop type mem_type;
Type dropped.
SQL>