Bulk Collect with Limit Clause and %NOTFOUND

With is blog post I am trying to demonostrate the issue of using %NOTFOUND with BULK COLLECT and LIMIT clause. When we use %NOTFOUND with BULK COLLECT and LIMIT Clause we need to be cautious about the placement of %NOTFOUND.

Let me run the classic CURSOR LOOP on EMP Table first. Do not forget to set serveroutput on before running following examples.

SQL> declare
  2     cursor c1 is select * from emp;
  3     l_row c1%rowtype;
  4     cnt number := 0;
  5  begin
  6     open c1;
  7     loop
  8             fetch c1 into l_row;
  9             exit when c1%notfound;
 10             cnt := cnt+1;
 11             dbms_output.put_line(cnt || '. ' || l_row.ename || '(' || l_row.empno || ')' );
 12     end loop;
 13     close c1;
 14  end;
 15  /
1. KING(7839)
2. BLAKE(7698)
3. CLARK(7782)
4. JONES(7566)
5. SCOTT(7788)
6. FORD(7902)
7. SMITH(7369)
8. ALLEN(7499)
9. WARD(7521)
10. MARTIN(7654)
11. TURNER(7844)
12. ADAMS(7876)
13. JAMES(7900)
14. MILLER(7934)
PL/SQL procedure successfully completed.

As we can see we have 14 rows in EMP table. Lets modify the above code to run it using BULK COLLECT with limit of 5 rows.

SQL> declare
  2     cursor c1 is select * from emp;
  3     type tt is table of c1%rowtype index by binary_integer;
  4     t tt;
  5     cnt number := 0;
  6  begin
  7     open c1;
  8     loop
  9             fetch c1 bulk collect into t limit 5;
 10             exit when c1%notfound;
 11
 12             for i in 1..t.count
 13             loop
 14                     cnt := cnt+1;
 15                     dbms_output.put_line(cnt || '. ' || t(i).ename || '(' || t(i).empno || ')');
 16             end loop;
 17     end loop;
 18     close c1;
 19  end;
 20  /
1. KING(7839)
2. BLAKE(7698)
3. CLARK(7782)
4. JONES(7566)
5. SCOTT(7788)
6. FORD(7902)
7. SMITH(7369)
8. ALLEN(7499)
9. WARD(7521)
10. MARTIN(7654)
PL/SQL procedure successfully completed.

Ouch !!! It only displayed 10 records, where are my last 4 rows? The problem is we are using "exit when c1%notfound;" just after bulk collect. In the 3rd iteration when Oracle bulk collected the last 4 rows and cursor got exhausted, Oracle Set c1%notfound as true and we exited from our for loop.

So How to fix it? It is easy, simply put your "exit when c1%notfound;" just before the end loop of Cursor Loop. Lets try it.

SQL> declare
  2     cursor c1 is select * from emp;
  3     type tt is table of c1%rowtype index by binary_integer;
  4     t tt;
  5     cnt number := 0;
  6  begin
  7     open c1;
  8     loop
  9             fetch c1 bulk collect into t limit 5;
 10
 11             for i in 1..t.count
 12             loop
 13                     cnt := cnt+1;
 14                     dbms_output.put_line(cnt || '. ' || t(i).ename || '(' || t(i).empno || ')');
 15             end loop;
 16             exit when c1%notfound;
 17     end loop;
 18     close c1;
 19  end;
 20  /
1. KING(7839)
2. BLAKE(7698)
3. CLARK(7782)
4. JONES(7566)
5. SCOTT(7788)
6. FORD(7902)
7. SMITH(7369)
8. ALLEN(7499)
9. WARD(7521)
10. MARTIN(7654)
11. TURNER(7844)
12. ADAMS(7876)
13. JAMES(7900)
14. MILLER(7934)
PL/SQL procedure successfully completed.

Alternatively we can also use collection.count to check if collection is empty after bulk collect operation, instead of using "%NOTFOUND" as in the following example.

SQL> declare
  2     cursor c1 is select * from emp;
  3     type tt is table of c1%rowtype index by binary_integer;
  4     t tt;
  5     cnt number := 0;
  6  begin
  7     open c1;
  8     loop
  9             fetch c1 bulk collect into t limit 5;
 10             exit when t.count = 0;
 11             for i in 1..t.count
 12             loop
 13                     cnt := cnt+1;
 14                     dbms_output.put_line(cnt || '. ' || t(i).ename || '(' || t(i).empno || ')');
 15             end loop;
 16
 17     end loop;
 18     close c1;
 19  end;
 20  /
1. KING(7839)
2. BLAKE(7698)
3. CLARK(7782)
4. JONES(7566)
5. SCOTT(7788)
6. FORD(7902)
7. SMITH(7369)
8. ALLEN(7499)
9. WARD(7521)
10. MARTIN(7654)
11. TURNER(7844)
12. ADAMS(7876)
13. JAMES(7900)
14. MILLER(7934)
PL/SQL procedure successfully completed.

I hope that you have enjoyed reading this article. Feedbacks would be well appriciated.

Realted Links:
- PLSQL Tuning: Bulk Collect with Dynamic SQL
- PLSQL Tuning: Bind Variables and execute immediate
- DBMS_PROFILER: How to analyze pl/sql performance
- How to Get Execution Plan and Statistics of SQL Query
- Query optimization tips for Oracle