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;

God's Word for the day

The need of reflection and self control

Let nothing hinder you from paying a vow promptly,

  and do not wait until death to be released from it.

Before making a vow, prepare yourself;

  And do not be like one who puts the Lord to the test.

Think of his wrath on the day of death,

  and of the moment of vengeance when he turns away his face.

In the time of plenty, think of the time of hunger;

  In days of wealth think of poverty and need.

From morning to evening conditions change;

  All things move swiftly before the Lord 

Sirach 18:22-26

Hearers and Doers 

Everyone who hears these words of mine and acts on them

  Will be like a wise man who built his house on rock.

The rain fell, the floods came and the winds blew

  and beat on that house, but it did not fall,

Because it had been founded on rock.

Mathew 7:24-25