oracle-databaseplsqlnested-tablebulk-collectrowtype

How to set a Limit on Bulk Collect with a Nested Table PL/SQL Collection?

I'm trying to set a limit on a database query with the limit keyword.

This is my current working query without the limit keyword.

DECLARE
   TYPE NESTED_TABLE_DECLARATION IS TABLE OF SCHEMA.TABLE_NAME%ROWTYPE;
   NESTED_TABLE NESTED_TABLE_DECLARATION;
BEGIN
   SELECT * BULK COLLECT INTO NESTED_TABLE FROM SCHEMA.TABLE_NAME;
END;
/

I want to avoid using a cursor but still use the limit keyword like below.

DECLARE
   TYPE NESTED_TABLE_DECLARATION IS TABLE OF SCHEMA.TABLE_NAME%ROWTYPE;
   NESTED_TABLE NESTED_TABLE_DECLARATION;
BEGIN
   SELECT * BULK COLLECT LIMIT 100 INTO NESTED_TABLE FROM SCHEMA.TABLE_NAME;
END;
/
Solution

  • It is not possible to use the reserved word Limit in your main query, this only works using a cursor for the bulk collection to optimize the memory of your server. In your case, use Rownum < 100 in the Where.

    SELECT * BULK COLLECT INTO NESTED_TABLE FROM SCHEMA.TABLE_NAME WHERE rownum <= 1000;

    Or Oracle12c

    SELECT * BULK COLLECT INTO NESTED_TABLE FROM SCHEMA.TABLE_NAME FETCH FIRST 1000 ROWS ONLY;