Mega Code Archive
COLLECT Operator
<![CDATA[
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> drop table employee;
Table dropped.
SQL> drop type nestedTableType;
Type dropped.
SQL>
]]>