Oracle PL/SQL Tutorial/Collections/COLLECT

COLLECT Operator

You use the COLLECT operator to get a list of values as a nested table.

You can cast the returned nested table to a nested table type using the CAST operator.

The following query illustrates the use of COLLECT:

SQL>
SQL> CREATE Or Replace TYPE nestedTableType IS TABLE OF VARCHAR2(10)
  2  /
Type created.
SQL>
SQL> CREATE TABLE employee (
  2    id         INTEGER PRIMARY KEY,
  3    first_name VARCHAR2(10),
  4    last_name  VARCHAR2(10),
  5    addresses  nestedTableType
  6  )
  7  NESTED TABLE
  8    addresses
  9  STORE AS
 10    nested_addresses2 TABLESPACE users;
Table created.
SQL> SELECT tablespace_name
  2  FROM user_tablespaces
  3  /
TABLESPACE_NAME
------------------------------
SYSTEM
UNDO
SYSAUX
TEMP
USERS
SQL>
SQL> SELECT COLLECT(first_name)
  2  FROM employee;
COLLECT(FIRST_NAME)
----------------------------------
SYSTP3ppbcSo4QhS0YU4yNeNpiA==()
SQL>
SQL>
SQL> drop table employee;
Table dropped.
SQL> drop type nestedTableType;
Type dropped.
SQL>