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,
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).