BULK COLLECT

CREATE OR REPLACE

PROCEDURE EMP_BULKCOLLECT

as

 

  type TY_EMP IS TABLE OF EMP%ROWTYPE;

  V_EMP TY_EMP;

BEGIN

  SELECT * BULK COLLECT INTO V_EMP FROM EMP;

  FOR i IN 1 .. V_EMP.COUNT

  LOOP

    DBMS_OUTPUT.PUT_LINE(i|| '   ' ||V_EMP(i).ENAME);

  END LOOP;

END;

The most important thing to remember when you learn about and start to take advantage of features such as BULK COLLECT is that there is no free lunch. There is almost always a trade-off to be made somewhere. The tradeoff with BULK COLLECT, like so many other performance-enhancing features, is "run faster but consume more memory."

Specifically, memory for collections is stored in the program global area (PGA), not the system global area (SGA). SGA memory is shared by all sessions connected to 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, in addition to the memory allocated to the SGA.

Consequently, you should avoid this sort of "unlimited" use of BULK COLLECT. Instead, move the SELECT statement into an explicit cursor declaration and then use a simple loop to fetch many, but not all, rows from the table with each execution of the loop body, as shown in below example.

CREATE OR REPLACE

PROCEDURE EMP_BULKCOLLECT_LIMIT(

    limit_in IN PLS_INTEGER)

as

  type TY_EMP IS  TABLE OF EMP%ROWTYPE;

  V_EMP TY_EMP;

 

  CURSOR C1

  IS

    SELECT * FROM EMP;

BEGIN

  OPEN C1;

  FETCH C1 BULK COLLECT INTO V_EMP LIMIT LIMIT_IN;

  FOR i IN 1 .. V_EMP.COUNT

  LOOP

    DBMS_OUTPUT.PUT_LINE(i|| '   ' ||V_EMP(i).ENAME);

  END LOOP;

  close C1;

END;