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:

   <source lang="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> 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></source>