16f6c0aecf
Inline adoc files when they are included exactly once. Also fix language tags because this inlining gives us better information on what language the code is written in.
74 lines
1.6 KiB
Plaintext
74 lines
1.6 KiB
Plaintext
== Why is this an issue?
|
|
|
|
A ``++FETCH ... BULK COLLECT INTO++`` without a ``++LIMIT++`` clause will load all the records returned by the cursor at once. This may lead to memory exhaustion. Instead, it is better to process the records in chunks using the ``++LIMIT++`` clause.
|
|
|
|
|
|
=== Noncompliant code example
|
|
|
|
[source,sql]
|
|
----
|
|
SET SERVEROUTPUT ON
|
|
|
|
-- Fetches all records at once, requiring lots of memory
|
|
DECLARE
|
|
TYPE largeTableRowArrayType IS TABLE OF largeTable%ROWTYPE;
|
|
largeTableRowArray largeTableRowArrayType;
|
|
CURSOR myCursor IS SELECT * FROM largeTable;
|
|
BEGIN
|
|
OPEN myCursor;
|
|
|
|
FETCH myCursor BULK COLLECT INTO largeTableRowArray; -- Non-compliant
|
|
|
|
DBMS_OUTPUT.PUT_LINE('Alternative 1: ' || largeTableRowArray.COUNT || ' records');
|
|
|
|
CLOSE myCursor;
|
|
END;
|
|
/
|
|
----
|
|
|
|
|
|
=== Compliant solution
|
|
|
|
[source,sql]
|
|
----
|
|
SET SERVEROUTPUT ON
|
|
|
|
-- fetches one chunk at a time, requiring constant memory
|
|
DECLARE
|
|
TYPE largeTableRowArrayType IS TABLE OF largeTable%ROWTYPE;
|
|
largeTableRowArray largeTableRowArrayType;
|
|
CURSOR myCursor IS SELECT * FROM largeTable;
|
|
counter PLS_INTEGER := 0;
|
|
BEGIN
|
|
OPEN myCursor;
|
|
|
|
LOOP
|
|
FETCH myCursor BULK COLLECT INTO largeTableRowArray LIMIT 1000; -- Compliant
|
|
|
|
counter := counter + largeTableRowArray.COUNT;
|
|
|
|
EXIT WHEN myCursor%NOTFOUND;
|
|
END LOOP;
|
|
|
|
DBMS_OUTPUT.PUT_LINE('Alternative 1: ' || counter || ' records');
|
|
|
|
CLOSE myCursor;
|
|
END;
|
|
/
|
|
|
|
DROP TABLE largeTable;
|
|
----
|
|
|
|
ifdef::env-github,rspecator-view[]
|
|
|
|
'''
|
|
== Implementation Specification
|
|
(visible only on this page)
|
|
|
|
=== Message
|
|
|
|
Add a "LIMIT" clause to the statement.
|
|
|
|
|
|
endif::env-github,rspecator-view[]
|