Retrieving array of rows


In 8.1 array fetching has been added. For example:

DECLARE
    TYPE enameType IS TABLE OF emp.ename%type;
    TYPE empnoType IS TABLE OF emp.empno%type;

    enames  enameType;
    empnos  empnoType;
BEGIN
    SELECT ename, empno BULK COLLECT INTO enames, empnos FROM emp;
END;
/

As of 8.1, it is not possible to limit how many rows are processed in a single pass, so if you know that you may be retrieving massive amounts of rows, it would be advisable to fall back to good ol' dynamic SQL.


Update 1:

Thanks to Adrian Billington for pointing out that from 8.1.6 Oracle added the LIMIT clause to allow better control of the bulk fetch. I've paraphrased the example he sent me to:

    OPEN my_cursor;
    LOOP
        FETCH my_cursor
        BULK COLLECT INTO plsql_collection LIMIT 10;
           /* ...do some processing... */ 
        EXIT WHEN my_cursor%NOTFOUND;
    END LOOP;
    CLOSE my_cursor;
He also points out the importance of the EXIT WHEN clause after the BULK COLLECT clause to ensure that the last set of results (which will typically be less than the value specified in the LIMIT) are successfully received and processed.


Update 2: Oracle 9i introduces further enhancements to the BULK COLLECT clause, described below

BULK_ROWCOUNT

Within a FORALL operation, you can now probe the results of each iteration bulk processed SQL within the loop. The BULK_ROWCOUNT is a collection in its own right defined with the same array indices as the driving array. An example from the doco:

DECLARE
   TYPE NumList IS TABLE OF NUMBER;
   depts NumList := NumList(10, 20, 50);
BEGIN
   FORALL j IN depts.FIRST..depts.LAST
      UPDATE emp SET sal = sal * 1.10 WHERE deptno = depts(j);
-- Did the 3rd UPDATE statement affect any rows?
   IF SQL%BULK_ROWCOUNT(3) = 0 THEN ...
END;

BULK_EXCEPTIONS

Similarly, if any of the individual iterations encounter an error, you can collectively retain all of the exceptions that are raised. An example from the doco:

DECLARE 
   TYPE NumList IS TABLE OF NUMBER;
   num_tab NumList := NumList(10,0,11,12,30,0,20,199,2,0,9,1);
   errors  NUMBER;
BEGIN
   FORALL i IN num_tab.FIRST..num_tab.LAST SAVE EXCEPTIONS
      DELETE * FROM emp WHERE sal > 500000/num_tab(i);
EXCEPTION WHEN OTHERS THEN    -- this is not in the doco, thanks to JL for pointing this out
   errors := SQL%BULK_EXCEPTIONS.COUNT;
   dbms_output.put_line('Number of errors is ' || errors);
   FOR i IN 1..errors LOOP
         -- Iteration is SQL%BULK_EXCEPTIONS(i).ERROR_INDEX;
         -- Error code is SQL%BULK_EXCEPTIONS(i).ERROR_CODE;
   END LOOP;
END;

Native Dynamic SQL

Possible the nicest addition in 9i is that bulk operations have been incorporated into native dynamic SQL

EXECUTE IMMEDIATE dynamic_string
   [[BULK COLLECT] INTO define_variable[, define_variable ...]]
   [USING bind_argument[, bind_argument ...]]
   [{RETURNING | RETURN} 
   BULK COLLECT INTO bind_argument[, bind_argument ...]];