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...