CREATE OR REPLACE TYPE DATE_RANGE IS OBJECT ( start_date NUMBER( 8 ), end_date NUMBER( 8 ) );

CREATE OR REPLACE TYPE DATE_RANGE_ARR IS TABLE OF DATE_RANGE;

DECLARE
   g_splited_range_arr   DATE_RANGE_ARR := DATE_RANGE_ARR( );
   g_travel_range        DATE_RANGE := DATE_RANGE( '20110101', '99991231' );
   g_bo_arr              DATE_RANGE_ARR := DATE_RANGE_ARR( DATE_RANGE( '20110312', '20110317' ), DATE_RANGE( '20110315', '20110329' ) );

   FUNCTION split_date_sql( pr_travel_date_range    DATE_RANGE,
                            pr_bo_arr               DATE_RANGE_ARR )
      RETURN DATE_RANGE_ARR
   IS
      l_splited_range_arr   DATE_RANGE_ARR;
   BEGIN
      SELECT start_date, end_date
        INTO l_splited_range_arr(start_date, end_date)
        FROM (WITH all_dates
                      AS (SELECT tr_start_date AS a_date, 0 AS black_out_val FROM TABLE( pr_travel_date_range )
                          UNION ALL
                          SELECT tr_end_date, 0 FROM TABLE( pr_travel_date_range )
                          UNION ALL
                          SELECT bo_start_date - 1, 1 FROM TABLE( pr_bo_arr )
                          UNION ALL
                          SELECT bo_end_date + 1, -1 FROM TABLE( pr_bo_arr )),
                   got_analytics
                      AS (SELECT a_date AS start_date,
                                 LEAD( a_date ) OVER (ORDER BY a_date, black_out_val) AS end_date,
                                 SUM( black_out_val ) OVER (ORDER BY a_date, black_out_val) AS black_out_cnt
                            FROM all_dates)
                SELECT start_date, end_date
                  FROM got_analytics
                 WHERE black_out_cnt = 0 AND start_date < end_date
              ORDER BY start_date);

      RETURN l_splited_range_arr;
   END;
BEGIN

    g_splited_range_arr := split_date_sql(g_travel_range,g_bo_arr);
   
    FOR index_g_splited_range_arr IN g_splited_range_arr .FIRST .. g_splited_range_arr .LAST LOOP        
        DBMS_OUTPUT.PUT_LINE('g_splited_range_arr[' || index_g_splited_range_arr || ']: ' || g_splited_range_arr(index_g_splited_range_arr).start_date || '-'  || g_splited_range_arr(index_g_splited_range_arr).end_date );
    END LOOP;
   
EXCEPTION
   WHEN NO_DATA_FOUND
   THEN
      NULL;
   WHEN OTHERS
   THEN
      NULL;
END;

SELECT  *
  BULK COLLECT INTO g_splited_range_arr
  FROM view_split_date(g_travel_range,g_bo_arr);

   WHEN OTHERS
   THEN
      NULL;

SCOTT@orcl_11gR2> CREATE OR REPLACE TYPE DATE_RANGE IS OBJECT
  2    ( start_date NUMBER( 8 ),
  3  	 end_date NUMBER( 8 ) );
  4  /

Type created.

SCOTT@orcl_11gR2> CREATE OR REPLACE TYPE DATE_RANGE_ARR IS TABLE OF DATE_RANGE
  2  /

Type created.

SCOTT@orcl_11gR2> VARIABLE g_refcur REFCURSOR
SCOTT@orcl_11gR2> DECLARE
  2  	g_splited_range_arr   DATE_RANGE_ARR := DATE_RANGE_ARR( );
  3  	g_travel_range	      DATE_RANGE := DATE_RANGE( '20110101', '99991231' );
  4  BEGIN
  5    OPEN :g_refcur FOR SELECT * FROM TABLE ( g_travel_range );
  6  END;
  7  /
  OPEN :g_refcur FOR SELECT * FROM TABLE ( g_travel_range );
                                   *
ERROR at line 5:
ORA-06550: line 5, column 36:
PL/SQL: ORA-22905: cannot access rows from a non-nested table item
ORA-06550: line 5, column 22:
PL/SQL: SQL Statement ignored

SCOTT@orcl_11gR2> DECLARE
  2  	g_splited_range_arr   DATE_RANGE_ARR := DATE_RANGE_ARR( );
  3  	g_travel_range	      DATE_RANGE_ARR := DATE_RANGE_ARR
  4  						  ( DATE_RANGE( '20110101', '99991231' ));
  5  BEGIN
  6    OPEN :g_refcur FOR SELECT * FROM TABLE ( g_travel_range );
  7  END;
  8  /

PL/SQL procedure successfully completed.

SCOTT@orcl_11gR2> PRINT g_refcur

START_DATE   END_DATE
---------- ----------
  20110101   99991231

1 row selected.

SCOTT@orcl_11gR2>

CREATE OR REPLACE TYPE DATE_RANGE IS OBJECT ( start_date NUMBER( 8 ), end_date NUMBER( 8 ) );

CREATE OR REPLACE TYPE DATE_RANGE_ARR IS TABLE OF DATE_RANGE;

DECLARE
   g_splited_range_arr   DATE_RANGE_ARR;
   g_travel_range        DATE_RANGE_ARR := DATE_RANGE_ARR( DATE_RANGE( '20110101', '99991231' ) );
   g_bo_arr              DATE_RANGE_ARR := DATE_RANGE_ARR( DATE_RANGE( '20110312', '20110317' ), DATE_RANGE( '20110315', '20110329' ) );

   FUNCTION split_date_sql( pr_travel_date_range DATE_RANGE_ARR, pr_bo_arr DATE_RANGE_ARR )
      RETURN DATE_RANGE_ARR
   IS
      l_splited_range_arr   DATE_RANGE_ARR := DATE_RANGE_ARR( );
      l_date                DATE_RANGE := DATE_RANGE( NULL, NULL );
   BEGIN
   
      -- Used cursor for inserting into another Nested Table=l_splited_range_arr
      FOR rec_splited_date
         IN ( WITH 
                   nt_travel AS (SELECT * FROM TABLE( pr_travel_date_range )),   -- Select from Nested Table
                   nt_black_out_dates AS (SELECT * FROM TABLE( pr_bo_arr )),     -- Select from Nested Table
                   all_dates AS (SELECT start_date AS a_date, 0 AS black_out_val FROM nt_travel    -- Used Nested Table in SQL
                                 UNION ALL
                                 SELECT end_date, 0 FROM nt_travel                                 -- Used Nested Table in SQL
                                 UNION ALL
                                 SELECT start_date - 1, 1 FROM nt_black_out_dates                  -- Used Nested Table in SQL
                                 UNION ALL
                                 SELECT end_date + 1, -1 FROM nt_black_out_dates),                 -- Used Nested Table in SQL
                   got_analytics
                      AS (SELECT a_date AS start_date,
                                 LEAD( a_date ) OVER (ORDER BY a_date, black_out_val) AS end_date,
                                 SUM( black_out_val ) OVER (ORDER BY a_date, black_out_val) AS black_out_cnt
                            FROM all_dates)
               SELECT start_date, end_date
                 FROM got_analytics
                WHERE black_out_cnt = 0 AND start_date < end_date
             ORDER BY start_date )
      LOOP
         l_date.start_date := rec_splited_date.start_date;
         l_date.end_date := rec_splited_date.end_date;
         -- Inserting into another Nested Table=l_splited_range_arr from cursor=rec_splited_date
         l_splited_range_arr.EXTEND;
         l_splited_range_arr( l_splited_range_arr.LAST ) := l_date;
      END LOOP;

      RETURN l_splited_range_arr;
   END;
BEGIN
   g_splited_range_arr := split_date_sql( g_travel_range, g_bo_arr );

   FOR index_g_splited_range_arr IN g_splited_range_arr.FIRST .. g_splited_range_arr.LAST
   LOOP
      DBMS_OUTPUT.PUT_LINE(
              'g_splited_range_arr['
           || index_g_splited_range_arr
           || ']: '
           || g_splited_range_arr( index_g_splited_range_arr ).start_date
           || '-'
           || g_splited_range_arr( index_g_splited_range_arr ).end_date );
   END LOOP;
END;