The FETCH ... INTO statement is inefficient when used in a loop (where many records are expected). It leads to many context-switches
between the SQL and PL/SQL engines. Instead, the FETCH ... BULK COLLECT INTO statement will issue the SQL requests in bulk, minimizing
context switches.
Noncompliant code example
SET SERVEROUTPUT ON
CREATE TABLE largeTable AS SELECT ROWNUM AS id FROM all_objects;
SET TIMING ON
DECLARE
x PLS_INTEGER;
CURSOR largeCursor IS SELECT ROWNUM FROM largeTable;
largeTableRowId BINARY_INTEGER;
BEGIN
OPEN largeCursor;
x := 0;
LOOP
FETCH largeCursor INTO largeTableRowId; -- Noncompliant
EXIT WHEN largeCursor%NOTFOUND;
x := x + largeTableRowId;
END LOOP;
DBMS_OUTPUT.PUT_LINE('Sum of rownums using alternative 1: ' || x);
CLOSE largeCursor;
END;
/
SET TIMING OFF
DECLARE
r largeTable%ROWTYPE;
CURSOR myCursor IS SELECT * FROM largeTable;
BEGIN
OPEN myCursor;
FETCH myCursor INTO r; -- Compliant, outside of a loop
CLOSE myCursor;
END;
/
DROP TABLE largeTable;
Compliant solution
SET SERVEROUTPUT ON
CREATE TABLE largeTable AS SELECT ROWNUM AS id FROM all_objects;
SET TIMING ON
DECLARE
x PLS_INTEGER;
CURSOR largeCursor IS SELECT * FROM largeTable;
TYPE largeTableRowIdArrayType IS TABLE OF BINARY_INTEGER INDEX BY BINARY_INTEGER;
largeTableRowIdArray largeTableRowIdArrayType;
BEGIN
OPEN largeCursor;
x := 0;
LOOP
FETCH largeCursor BULK COLLECT INTO largeTableRowIdArray LIMIT 1000; -- Compliant
FOR i IN largeTableRowIdArray.FIRST .. largeTableRowIdArray.LAST LOOP
x := x + largeTableRowIdArray(i);
END LOOP;
EXIT WHEN largeCursor%NOTFOUND;
END LOOP;
DBMS_OUTPUT.PUT_LINE('Sum of rownums using alternative 2: ' || x);
CLOSE largeCursor;
END;
/
SET TIMING OFF
DECLARE
r largeTable%ROWTYPE;
CURSOR myCursor IS SELECT * FROM largeTable;
BEGIN
OPEN myCursor;
FETCH myCursor INTO r; -- Compliant, outside of a loop
CLOSE myCursor;
END;
/
DROP TABLE largeTable;
