I do not know any articles, except the manual, but I will explain one "грабли" with
FETCH ... BULK COLLECT INTO ... [LIMIT rows];
most people step on. It is related to %FOUND and %NOTFOUND cursor attributes. Most people think the above cursor attributes are set when FETCH does not retrieve any rows. It is not true. Cursor attribute %FOUND is set to FALSE (and %NOTFOUND to TRUE) when FETCH does not retrieve REQUESTED number of rows. Since most of the time we fetch one row at a time, most people tend to think the above cursor attributes are set when no rows are retrieved. And example in PL/SQL manual on Using the LIMIT Clause is also misleading:
1.
2.
3.
4.
5.
6.
7.
8.
9.
10.
11.
12.
13.
14.
15.
16.
17.
In the example below, with each iteration of the loop, the FETCH statement fetches ten rows (or less) into index-by table empnos.
The previous values are overwritten.
DECLARE
TYPE NumTab IS TABLE OF NUMBER INDEX BY BINARY_INTEGER;
CURSOR c1 IS SELECT empno FROM emp;
empnos NumTab;
rows NATURAL := 10 ;
BEGIN
OPEN c1;
LOOP
/* The following statement fetches 10 rows (or less). */
FETCH c1 BULK COLLECT INTO empnos LIMIT rows;
EXIT WHEN c1%NOTFOUND;
...
END LOOP;
CLOSE c1;
END;
The example itself is correct. What it does not explain is that the actual row processing marked as ... in the example (between line EXIT WHEN c1%NOTFOUND; and END LOOP;) will not be executed for the last iteration if it does not fetch EXACTLY 10 rows. Assume table emp has 14 rows. First iteration will fetch 10 of them. Since it fetched 10 rows, c1%NOTFOUND = FALSE and fetched rows will be processd by loop logic (marked in the example as ...). Second iteration will fetch only 4 rows, therefore c1%NOTFOUND will be set to TRUE and llop will exit RIGHT AWAY. However if emp has 10*k rows (for example 20 rows) all table emp rows will be processed by loop logic.
MORAL: Do not use %FOUND %NOTFOUND to exit loop when fetching bulk with limit. Exit when element count in PL/SQL table you are fetching into is 0:
EXIT WHEN empnos.COUNT = 0;
SY.
