Using TABLE() to Treat a VArrays

The TABLE() function treats a collection as a series of rows.


CREATE TYPE t_varray AS VARRAY(3) OF VARCHAR2(50);
/

CREATE TABLE emp(
    id INTEGER PRIMARY KEY,
    first_name VARCHAR2(10),
    last_name VARCHAR2(10),
    addresses t_varray
);


INSERT INTO emp VALUES (1, 'Jason', 'Bond',t_varray_address('Main Street, small town, CA, 12345',
                                                            'Second Street, middle town, CA, 54321'
                                                           )
);

SQL> SELECT a.*
  2  FROM emp e, TABLE(e.addresses) a
  3  WHERE id = 1;

COLUMN_VALUE
--------------------------------------------------
Main Street, small town, CA, 12345
Second Street, middle town, CA, 54321

SQL>

Embed an entire SELECT statement inside TABLE().


SQL> SELECT *
  2  FROM TABLE(
  3    SELECT addresses FROM emp WHERE id = 1
  4  );

COLUMN_VALUE
--------------------------------------------------
Main Street, small town, CA, 12345
Second Street, middle town, CA, 54321

SQL>

Using Table() function within from clause


SQL> SELECT c.id, c.first_name, c.last_name, a.*
  2  FROM emp c, TABLE(c.addresses) a
  3  WHERE id = 1;

        ID FIRST_NAME LAST_NAME       COLUMN_VALUE
---------- ---------- -----------------------------------
         1 Jason      Bond  Main Street, small town, CA, 12345
         1 Jason      Bond  Second Street, middle town, CA, 54321


SQL>

Home »
Oracle »
PL/SQL »

Varrays: