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