Term: LIMIT

Definition:
The Oracle PL/SQL LIMIT clause is used to retrieve a reduced result set of a larger query done through BULK_COLLECT. Reducing the fetch (chunk) size minimizes the load on the server memory. Note that it can be used only in the bulk collect FETCH statements.

Example Syntax:

FETCH [CURSOR] BULK_COLLECT INTO [COLLECTION VARIABLE] LIMIT [SIZE]

The user is free to specify the LIMIT array size as it depends upon the complexity of operation on fetched rows, memory configuration and other simultaneous processes using the system memory.

Example Usage:

The PL/SQL block below uses a cursor which selects all 203,600 records from EMP_PF_REC table. Each loop fetches 10,000 rows from the result set. In this instance the loop will run for 21 cycles (the 21st loop will return the last 3600 records).

DECLARE



TYPE TAB_PF IS TABLE OF EMP_PF_REC.PF_BALANCE%TYPE;

L_PF TAB_PF;



CURSOR C IS

    SELECT PF_BALANCE

    FROM EMP_PF_REC;



BEGIN



OPEN C;

LOOP

FETCH C BULK_COLLECT INTO L_PF LIMIT 10000;

EXIT WHEN L_PF.COUNT=0;

...

...

END LOOP;

CLOSE C;

END;