Issue selecting from Associative Array using a Table Collection Expression

Question

I seem to be having an issue when using a Table Collection Expression to select from an Associative Array (Note: I am using Oracle 12c, so this is allowed: Oracle Documentation)

Take the following "simple" example:

First, create a package which declares a record and the associative array:

CREATE OR REPLACE PACKAGE table_test
IS
  TYPE pt_DateSpan IS RECORD
  (
    StartDate DATE,
    EndDate DATE
  );

  TYPE pt_DateSpanTable IS TABLE OF pt_DateSpan INDEX BY PLS_INTEGER;
END;
/

Then, I wrote the following anonymous block to test the functionality:

DECLARE
  l_tTest table_test.pt_DateSpanTable;
  
  PROCEDURE lp_outputAArray (p_aaInput table_test.pt_DateSpanTable) IS
    l_nTableSize INTEGER;
  BEGIN
    --I know I can use p_aaInput.COUNT, but I want to select using TABLE() to show that the functionality "works"
    SELECT COUNT(*)
    INTO l_nTableSize
    FROM TABLE(p_aaInput);
    dbms_output.put_line('Table Size: '||l_nTableSize);
  
    FOR i IN 1..p_aaInput.COUNT LOOP
      dbms_output.put_line(i||': '||to_char(p_aaInput(i).StartDate, 'MM/DD/YYYY')||' - '||to_char(p_aaInput(i).EndDate, 'MM/DD/YYYY'));
    END LOOP;
  END lp_outputAArray;
BEGIN
  --ADD RECORD TO ASSOCIATIVE ARRAY
  SELECT to_date('01/01/2000', 'MM/DD/YYYY'), to_date('01/01/2010', 'MM/DD/YYYY')
  BULK COLLECT INTO l_tTest
  FROM DUAL;
  
  lp_outputAArray(l_tTest);  
  
  --SELECT THE ASSOCIATIVE ARRAY INTO ITSELF
  SELECT t.StartDate, t.EndDate
  BULK COLLECT INTO l_tTest
  FROM TABLE(l_tTest) t;
  
  lp_outputAArray(l_tTest);
END;
/

This block produces the following output:

Table Size: 1
1: 01/01/2000 - 01/01/2010
Table Size: 0

My question is why is the second output not identical to the first?

Also, I realize that I don't need to use BULK COLLECT in most of this example, it is a simplified version of my actual code which does SELECT from actual tables.

My final goal was to use UNION ALL to allow me to append values to my Associative Array instead of replacing it when performing a series of SELECT statements. Something like this:

SELECT *
  BULK COLLECT INTO l_tTest
  FROM (SELECT t.StartDate, t.EndDate
        FROM TABLE(l_tTest) t
        UNION ALL
        SELECT to_date('01/01/2011', 'MM/DD/YYYY'), to_date('01/01/2019', 'MM/DD/YYYY')
        FROM DUAL);

I would appreciate any help you could provide.

Answer 1

It appears that when you use:

SELECT ...
BULK COLLECT INTO array
FROM   ...

Then the first thing that happens is that the array you BULK COLLECT INTO is re-initialised to an empty array.

Therefore, when you want to use it in the table collection expression it is already empty and no rows are generated.

---

Instead, you could use a non-associative array and use the MULTISET operators in PL/SQL:

CREATE OR REPLACE PACKAGE table_test
IS
  TYPE range IS RECORD
  (
    StartDate DATE,
    EndDate DATE
  );

  TYPE range_table IS TABLE OF range
                      --INDEX BY PLS_INTEGER
                      ;
END;
/

DECLARE
  l_ranges  table_test.range_table := table_test.range_table();
  l_ranges2 table_test.range_table := table_test.range_table();

  PROCEDURE output_ranges(
    range_array table_test.range_table
  )
  IS
    idx PLS_INTEGER;
  BEGIN
    dbms_output.put_line('Table Size: '||range_array.COUNT);
  
    idx := range_array.FIRST;
    LOOP
      EXIT WHEN idx IS NULL;
      dbms_output.put_line(
        idx||': '||range_array(idx).StartDate||' - '||range_array(idx).EndDate
      );
      idx := range_array.NEXT(idx);
    END LOOP;
  END output_ranges;
BEGIN
  l_ranges.EXTEND(2);
  l_ranges(1) := table_test.range(DATE '2000-01-01', DATE '2001-01-01');
  l_ranges(2) := table_test.range(DATE '2001-01-01', DATE '2002-01-01');

  l_ranges2.EXTEND(2);
  l_ranges2(1) := table_test.range(DATE '2002-01-01', DATE '2003-01-01');
  l_ranges2(2) := table_test.range(DATE '2003-01-01', DATE '2004-01-01');

  output_ranges(l_ranges);
  output_ranges(l_ranges2);
  
  l_ranges := l_ranges MULTISET UNION ALL l_ranges2;

  output_ranges(l_ranges);
END;
/

Which outputs:

Table Size: 2
1: 01-JAN-00 - 01-JAN-01
2: 01-JAN-01 - 01-JAN-02
Table Size: 2
1: 01-JAN-02 - 01-JAN-03
2: 01-JAN-03 - 01-JAN-04
Table Size: 4
1: 01-JAN-00 - 01-JAN-01
2: 01-JAN-01 - 01-JAN-02
3: 01-JAN-02 - 01-JAN-03
4: 01-JAN-03 - 01-JAN-04