Tuesday, June 14, 2016

How to use Limits for Bulk Collect (PL/SQL Collections)

DECLARE
   CURSOR c_emp_cur
   IS
      SELECT * FROM emp;

   TYPE emp_cur_tbl_typ IS TABLE OF c_emp_cur%ROWTYPE
      INDEX BY PLS_INTEGER;

   emp_cur_tbl                                  emp_cur_tbl_typ;
BEGIN
   OPEN c_emp_cur;

   LOOP
      FETCH c_emp_cur BULK COLLECT INTO emp_cur_tbl LIMIT 100;

      EXIT WHEN c_emp_cur%NOTFOUND;

      FOR indx IN 1 .. emp_cur_tbl.COUNT
      LOOP
         DBMS_OUTPUT.put_line( emp_cur_tbl( indx));
      END LOOP;
   END LOOP;

   CLOSE c_emp_cur;
END ;

No comments:

Post a Comment

Note: Only a member of this blog may post a comment.