BULK COLLECT in Oracle

The BULK COLLECT clause tell to SQL engine to bulk bind output collect before returning them to PL/SQL engine, we can use BULK COLLECT in SELECT INTO ,FETCH INTO ,RETURNING INTO clause.


BULK COLLECT in SELECT INTO Clause:

BULK COLLECT in FETCH INTO Clause:

BULK COLLECT in RETURNING Clause:

BULK COLLECT With LIMIT:
Collection is stored in the PGA memory, not the SGA memory. SGA memory is sared by all sessions connected by Oracle Database but PGA memory is allocated for each session. Thus if a program requires 5MB of memory to populate a collection and there are 100 simultaneous connections, that program causes the consumption of 500MB of PGA memory, i assition to the memory allocated to the SGA. 

To avoid this memory problem Oracle use the LIMIT clause. 
Example:

DECLARE

CURSOR emp_cur  IS 
 SELECT * FROM EMP;

TYPE emp_aat IS TABLE OF emp_cur%ROWTYPE
INDEX BY PLS_INTEGER;
l_emp emp_aat;
V_LIMIT_VAL NUMBER :=5;
BEGIN
OPEN emp_cur;
LOOP
FETCH emp_cur BULK COLLECT INTO l_emp LIMIT V_LIMIT_VAL;
FOR IDX IN 1..l_emp.COUNT
LOOP
 --write your logic
 null;
END LOOP;
END LOOP;
CLOSE emp_cur;

END;
/