Quick Search:
CODE
Oracle PL/SQL Code Library
JOBS
Find Or Post Oracle Jobs
FORUM
Oracle Discussion & Chat
 The Oracle PL/SQL LIMIT Clause      [Return To Index] Jump to:  
  Looking for the original pages? (formerly called "Morgan's Library") You can find them here.

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;


Related Links:

Related Code Snippets:
 
Home      :      Code Library      :      Sponsors      :      Privacy      :      Terms of Use      :      Contact Us 224 users online    © 2009 psoug.org
PSOUG LOGIN
Username: 
Password: 
Forgot your password?