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

8 comments:

  1. What is ref cursor and why we use it?

    ReplyDelete
  2. can u briefly explain about limit clause.
    thank u,

    ReplyDelete
  3. this wil always solve the missing records prblm...a lot askd in interviews....i also aak this a lot of times :)

    ReplyDelete
  4. This comment has been removed by the author.

    ReplyDelete
    Replies
    1. limit 1? why are you even bulk collecting then??

      Delete
  5. Nice article.I really enjoyed to read this article.Thanks for post.

    ReplyDelete
  6. Hi Nimish,

    I have a query
    1.Here you used " dbms_output.put_line(cnt || '. ' || t(i).ename || '(' || t(i).empno || ')');"
    to display array value with count.
    output : 1. KING(7839)

    But my query is how to concat array value with comma and store it into variable (e.g. "KING","BLAKE","CLARK","JONES","SCOTT","FORD").

    or If you know how to convert nested array or any type of array value into JSON ARRAY format
    (e.g. ["KING","BLAKE","CLARK","JONES","SCOTT","FORD"]);

    Please help me out as soon as possible.

    ReplyDelete
    Replies
    1. https://docs.oracle.com/en/database/oracle/oracle-database/12.2/adjsn/generation.html

      Delete