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

Question

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;
/

Is your intention that you're only ever going to get 100 rows from this query? If so, do you care which 100 rows you get or are you fine with any arbitrary 100 rows? — Justin Cave, May 14 '19 at 16:16
@ BobJarvis: 12.1 @ JustinCave: For future performance tuning I want to be able to change this number quickly. The table may contain 10000 records in some setups in others over a million. @ APC: I might need to use a dblink and cursors can't be passed over dblinks. — Matt, May 14 '19 at 18:39
Every SELECT defines a (implicit) cursor, so your statement is not clear for me. — Wernfried Domscheit, May 14 '19 at 19:28

Julian Ayola · Accepted Answer · 2019-05-14T23:05:37.673

2

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;

edited May 14 '19 at 23:05

answered May 14 '19 at 22:45

Julian Ayola

51
3

Can you add example of this? – victorpacheco3107 May 14 '19 at 22:51

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

1 Answers1