BULK COLLECT WITH LIMIT Clause

BULK COLLECT WITH LIMIT Clause:

 in example we take emp table,in emp table have total 14 records

 declare
  cursor c1 is select * from emp;
  type tt is table of c1%rowtype index by binary_integer;
  t tt;
  cnt number :=0;
begin
     open c1;
     loop
          fetch c1 bulk collect into t limit 5;
          exit when c1%notfound;
             for i in 1.. t.count 
             loop
                 cnt :=cnt+1;
                 dbms_output.put_line(cnt ||'    '||t(i).ename);
             end loop;
      end loop;
 close c1;
end;
/
1    SMITH
2    ALLEN
3    WARD
4    JONES
5    MARTIN
6    BLAKE
7    CLARK
8    SCOTT
9    KING
10    TURNER
PL/SQL procedure successfully completed.
note: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.

 method 1
declare
  cursor c1 is select * from emp;
  type tt is table of c1%rowtype index by binary_integer;
  t tt;
  cnt number :=0;
begin
     open c1;
     loop
          fetch c1 bulk collect into t limit 5;
                       for i in 1.. t.count 
             loop
                 cnt :=cnt+1;
                 dbms_output.put_line(cnt ||'    '||t(i).ename);
             end loop;
 exit when c1%notfound;
      end loop;
 close c1;
end;

/
1    SMITH
2    ALLEN
3    WARD
4    JONES
5    MARTIN
6    BLAKE
7    CLARK
8    SCOTT
9    KING
10    TURNER
11    ADAMS
12    JAMES
13    FORD
14    MILLER

PL/SQL procedure successfully completed.

2nd method :
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.


declare
  cursor c1 is select * from emp;
  type tt is table of c1%rowtype index by binary_integer;
  t tt;
  cnt number :=0;
begin
     open c1;
     loop
          fetch c1 bulk collect into t limit 5;
  exit when t.count=0;
                       for i in 1.. t.count 
             loop
                 cnt :=cnt+1;
                 dbms_output.put_line(cnt ||'    '||t(i).ename);
             end loop;
      end loop;
 close c1;
end;
/
1    SMITH
2    ALLEN
3    WARD
4    JONES
5    MARTIN
6    BLAKE
7    CLARK
8    SCOTT
9    KING
10    TURNER
11    ADAMS
12    JAMES
13    FORD
14    MILLER

PL/SQL procedure successfully completed.

Comments

Post a Comment

Popular posts from this blog

PRAGMA SERIALLY_REUSABLE

PRAGMA SERIALLY_REUSABLE : We want to maintain state of the global variable we are using pragma serially_reusable in packages. ------------with out serially reusable pragma in packages SQL> create or replace package pkg3            is                   g number(3):=27;            end pkg3;           / Package created. SQL> begin                pkg3.g :=30;           end;          / PL/SQL procedure successfully completed. SQL> begin                  dbms_output.put_line(pkg3.g);           end;          / 30 PL/SQL procedure successfully completed. -------------------------------------------- serially reusable in packages SQL> create...
Read more

COLLECTION METHODS

Collection methods: A collection method is a built-in function or procedure that operates on collections and is called using dot notation. Following are the functions used with collections. EXISTS COUNT LIMIT FIRST AND LAST PRIOR AND NEXT EXTEND TRIM DELETE Syntax: collection_name.method_name [(parameters)] Collection methods cannot be called from SQL statements. Only the EXISTS method can be used on a NULL collection. All other methods applied on a null collection raise the COLLECTION_IS_NULL error. 1.COUNT: COUNT method returns the number of elements with the space allocated in Varrays and Nested tables. The COUNT method displays number of elements in associate arrays. COUNT can be smaller than LIMIT for Varrays. Example: DECLARE --Index by table type    TYPE etab IS TABLE OF emp%ROWTYPE INDEX BY BINARY_INTEGER;    emptab  etab; --Nested Table Type    TYPE tab_type IS TABLE OF VARCHAR2 (10);    t...
Read more

CURSOR PARAMETERS

CURSOR WITH  PARAMETERS: A cursor can be declare with parameters. Note: cursor parameters can be assigned default values. the scope of the cursor  parameters is local to the cursor the mode of the  parameters can only be IN. Example: Declare   Cursor c1 (p_deptno  emp.deptno%type) is   select ename,sal,deptno   from emp   where deptno=p_deptno; begin     for i in c1 (10) loop     dbms_output.put_line(i.ename||' '||i.sal||'  '||i.deptno);     end loop;         for i in c1(20) loop         dbms_output.put_line(i.ename||' '||i.sal||'  '||i.deptno);         end loop;     dbms_output.put_line('hai'); end; /
Read more