Collections : Example of Associative Array with LIMIT clause

There are three types of collections

Associative arrays - In Oracle 7 release they were known as PLSQL tables and in Oracle 8/8i release there were called index by tables (because at the time of declaring them you use the "index by" clause). They are single dimensional, unbounded, sparse collections of  homogenous elements. 

Nested Tables - They are also single dimensional, unbounded collections of homogenous elements. They are initially dense but can become spare by deletions. They can be defined both in PLSQL and in the database.

Varrays - They are single dimensional collections of homogenous elements. They are always bounded and never sparse. When you define a Varray you must also specify the maximum number of elements it can contain. Like Nested tables they can be used in PLSQL and in the database.

Below is an example of using an associative array with the LIMIT condition to limit the number of elements collected in the associative array at a time.

create or replace PROCEDURE pr_coll_example_1 AS
  TYPE rec_employee IS RECORD
  (
  EMPLOYEE_ID	        NUMBER(6,0),
  EMPLOYEE_NAME       VARCHAR2(100),
  EMAIL	            VARCHAR2(25),
  PHONE               VARCHAR2(20),
  HIRE_DATE	        DATE,
  JOB_ID	            VARCHAR2(10),
  DEPARTMENT_ID	    NUMBER(4,0) 
  );

  TYPE tab_employee IS TABLE OF rec_employee INDEX BY PLS_INTEGER;
  
  vt_employees    tab_employee;  
  CURSOR C1
  IS
  SELECT 
    EMPLOYEE_ID,
    FIRST_NAME || ' ' || LAST_NAME EMPLOYEE_NAME,
    EMAIL,
    PHONE_NUMBER,
    HIRE_DATE,
    JOB_ID,
    DEPARTMENT_ID
  FROM EMPLOYEES
  ORDER BY EMPLOYEE_ID;  
BEGIN
    OPEN C1;
    LOOP --{
    FETCH C1 BULK COLLECT INTO vt_employees LIMIT 20;
    FOR i IN vt_employees.FIRST .. vt_employees.LAST
    LOOP --{
    /* Perform row wise operations here for batch of 20 records */
      dbms_output.put_line(vt_employees(i).EMPLOYEE_ID || ' ' || vt_employees(i).EMPLOYEE_NAME);
    END LOOP; --}
    EXIT WHEN C1%NOTFOUND;
    END LOOP; --}
    CLOSE C1;
    
END;