COLLECT Operator : COLLECT « Collections « Oracle PL/SQL Tutorial

1. Oracle PL/SQL Tutorial
2. Collections
3. COLLECT

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>