usage of LIMIT option in SELECT ... BULK COLLECT INTO

Question

Starting from the answer of this question (Use Bulk Collect result in a select query without cursor), I am wondering if it is possible to use the LIMIT option in SELECT ... BULK COLLECT INTO ...

I know the possibility to use an explicit cursor but I would like to know if it is possible using a direct select statement.

Thank you,

Answer 1

From the documentation:

A SELECT BULK COLLECT INTO statement that returns a large number of rows produces a large collection. To limit the number of rows and the collection size, use one of these:

• ROWNUM pseudocolumn (described in Oracle Database SQL Language Reference)

• SAMPLE clause (described in Oracle Database SQL Language Reference)

• FETCH FIRST clause (described in Oracle Database SQL Language Reference)

So from the example in the previous question you linked to, you could do:

SELECT id BULK COLLECT INTO result_bulk FROM table1 WHERE rownum <= 1000;

or if you're on 12c:

SELECT id BULK COLLECT INTO result_bulk FROM table1 FETCH FIRST 1000 ROWS ONLY;

possibly with an order-by (in a subquery in the first version) for it to be deterministic.

The advantage of the explicit fetch version with the limit clause is that you can do that in a loop and keep fetching the next 1000 (or however many) rows until you've seen them all. With the select version you only get one shot; unless you put that in a loop and handle paging, and even then as each select is independent the data could change between queries (unless you also change the isolation level).