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
/
/*Create table T_SEGMENTS*/
CREATE TABLE t_segments
AS
SELECT segment_name, segment_type, tablespace_name, bytes, blocks
FROM user_segments
/

We'll create the nested table collection to be used in the string:

CREATE TYPE nt_string AS TABLE OF VARCHAR2...