title: Add elements one at a time from PL/SQL variable into collection variable?
body: <p>I have a routine written in T-SQL for SQL Server. We are migrating to Oracle so I am trying to port it to PL/SQL. Here is the T-SQL routine (simplified); note the use of the table-valued variable which, in Oracle, will become a "nested table" type PL/SQL variable. The main thrust of my question is on the best ways of working with such "collection" objects within PL/SQL. Several operations in the ported code (second code sample, below) are quite awkward, where they seemed a lot easier in the SQL Server original: </p>
  
  <pre><code>DECLARE @MyValueCollection TABLE( value VARCHAR(4000) );
  DECLARE @valueForThisRow VARCHAR(4000);
  
  DECLARE @dataItem1Val INT, @dataItem2Val INT, @dataItem3Val INT, @dataItem4Val INT;
  DECLARE theCursor CURSOR FAST_FORWARD FOR
    SELECT DataItem1, DataItem2, DataItem3, DataItem4 FROM DataTable;
  
  OPEN theCursor;
  
  FETCH NEXT FROM theCursor INTO @dataItem1Val, @dataItem2Val, @dataItem3Val, @dataItem4Val;
  
  WHILE @@FETCH_STATUS = 0
  BEGIN
    -- About 50 lines of logic that evaluates @dataItem1Val, @dataItem2Val, @dataItem3Val, @dataItem4Val and constructs @valueForThisRow
    SET @valueForThisRow = 'whatever';
  
    -- !!! This is the row that seems to have no natural Oracle equivalent
    INSERT INTO @MyValueCollection VALUES(@valueForThisRow);  
  
    FETCH NEXT FROM theCursor INTO @dataItem1Val, @dataItem2Val, @dataItem3Val, @dataItem4Val;
  END;
  
  CLOSE theCursor;
  DEALLOCATE theCursor;
  
  -- !!! output all the results; this also seems harder than it needs to be in Oracle
  SELECT * FROM @MyValueCollection;
  </code></pre>
  
  <p>I have been able to port pretty much everything, but in two places (see comments in the code), the logic is a lot more complex than the old SQL Server way, and I wonder if there might be, in Oracle, some more graceful way that is eluding me:</p>
  
  <pre><code>set serveroutput on; -- needed for DBMS_OUTPUT; see below
  
  DECLARE
    TYPE StringList IS TABLE OF VARCHAR2(4000);
    myValueCollection StringList;
    dummyTempCollection StringList; -- needed for my kludge; see below
    valueForThisRow VARCHAR2(4000);
  BEGIN
    -- build all the sql statements
    FOR c IN (
      SELECT DataItem1, DataItem2, DataItem3, DataItem4 FROM DataTable;
    )
    LOOP
      -- About 50 lines of logic that evaluates c.DataItem1, c.DataItem2, c.DataItem3, c.DataItem4 and constructs valueForThisRow
      valueForThisRow := 'whatever';
  
      -- This seems way harder than it should be; I would rather not need an extra dummy collection
      SELECT valueForThisRow BULK COLLECT INTO dummyTempCollection FROM dual;    -- overwrites content of dummy temp
      myValueCollection := myValueCollection MULTISET UNION dummyTempCollection; -- merges into main collection
  
    END LOOP;
  
    -- output all the results... again, there's no shorter/easier/more-compact/single-line equivalent?
    IF myValueCollection.COUNT &gt; 0
    THEN
      FOR indx IN myValueCollection.FIRST .. myValueCollection.LAST
      LOOP
         DBMS_OUTPUT.PUT_LINE(myValueCollection(indx));
      END LOOP;
    END IF;
  END;
  /
  </code></pre>
  
  <p>Thanks in advance for any help!</p>