Add elements one at a time from PL/SQL variable into collection variable?

Question

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:

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;

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:

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 > 0
  THEN
    FOR indx IN myValueCollection.FIRST .. myValueCollection.LAST
    LOOP
       DBMS_OUTPUT.PUT_LINE(myValueCollection(indx));
    END LOOP;
  END IF;
END;
/

Thanks in advance for any help!

Maybe a TEMPORARY TABLE can help you here. See http://stackoverflow.com/questions/1573877/selecting-values-from-oracle-table-variable-array — Stephane Lallemagne, Dec 28 '13 at 00:52
what do you mean by 'routine'? stored procedure? Also, where are you outputing the final select? To a .net app? — Sebas, Dec 28 '13 at 01:12
How many rows does this routine SELECT and INSERT each time it's invoked? — Bob Jarvis - Слава Україні, Dec 28 '13 at 01:31
In that case, I'd try to put the 50 lines of code directly into the SELECT (you can do a lot with CASE expressions - perhaps enough and perhaps not, but worth investigating), and then just use an INSERT...SELECT... to slam the data directly into the table. YMMV. Share and enjoy. — Bob Jarvis - Слава Україні, Dec 30 '13 at 11:30

score 8 · Accepted Answer · answered Dec 28 '13 at 00:59

Personally, I'd take the "50 lines of logic", move it into a function that you call in your SQL statement, and then do a simple BULK COLLECT to load the data into your local collection.

Assuming that you really want to load data element-by-element into the collection, you can simplify the code that loads the collection

DECLARE
  TYPE StringList IS TABLE OF VARCHAR2(4000);
  myValueCollection StringList := StringList();
  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';

    myValueCollection.extend();
    myValueCollection( myValueCollection.count ) := valueForThisRow;
  END LOOP;

  -- output all the results... again, there's no shorter/easier/more-compact/single-line equivalent?
  IF myValueCollection.COUNT > 0
  THEN
    FOR indx IN myValueCollection.FIRST .. myValueCollection.LAST
    LOOP
       DBMS_OUTPUT.PUT_LINE(myValueCollection(indx));
    END LOOP;
  END IF;
END;
/

If you declare the collection as an associative array, you could avoid calling extend to increase the size of the collection. If you know the number of elements that you are going to load into the collection, you could pass that to a single extend call outside the loop. Potentially, you can also eliminate the valueForThisRow local variable and just operate on elements in the collection.

As for the code that processes the collection, what is it that you are really trying to do? It would be highly unusual for production code to write to dbms_output and expect that anyone will see the output during normal processing. That will influence the way that you would write that code. Assuming that your intention is really to just call dbms_output, knowing that will generally send the data into the ether

FOR indx IN 1 .. myValueCollection.count
LOOP
  dbms_output.put_line( myValueCollection(indx) );
END LOOP;

This works when you have a dense collection (all indexes between 1 and the count of the collection exist and have values). If you might have a sparse collection, you would want to use FIRST, NEXT, and LAST in a loop but that's a bit more code.

Thanks for all the helpful info. I like the function idea, but my directive for the port is to change as little as possible, so I appreciate your suggestions for how to simplify what I've got. What I'm doing with the dbms_output is spooling it to a temp file that another (non-Oracle) job then picks up and processes further. If there are problems with using it in production code for an application like that, please let me know about that as well! — Robert N, Dec 28 '13 at 01:30
@RobertN - Writing to a file would normally be done via `utl_file`. That will write a file on the database server. It's not clear to me whether that is what you are trying to do or whether you are trying to use SQL*Plus to write to a file on the client. — Justin Cave, Dec 28 '13 at 03:29
LOL, yes, Justin, that's exactly what happened! I originally wrote the ported version using UTL_FILE, but the process that needs to pick up the produced file is on the client side, so I would prefer to write out the file on the client side as well. I could open up a file share on the database server, but our security folks would prefer I not do that. Hence, the whole reason for this question. :-) — Robert N, Dec 28 '13 at 16:30

Add elements one at a time from PL/SQL variable into collection variable?

1 Answers1