Advanced Oracle PL/SQL Developer's Guide - Second Edition

Oracle 12c enhancements to collections

Oracle Database 12c allows a join between a table and a collection type. If an SQL collection type is used as a return type of a function, the table and the function output can be joined using CROSS APPLY and OUTER APPLY. The function must use a value from the joining table as a parameter and return a collection variable of a nested table or varray type.

For the purpose of illustration, let us create the test tables using dictionary views from the Oracle Database. The table T_TBS_OBJ contains the tablespace information and T_SEGMENTS contains the segments created on these tablespaces:

/*Create table T_TBS_OBJ*/ CREATE TABLE t_tbs_obj AS SELECT tablespace_name, status, allocation_type FROM user_tablespaces / ...