0

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,

mikcutu
  • 1,013
  • 2
  • 17
  • 34

1 Answers1

2

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

Community
  • 1
  • 1
Alex Poole
  • 183,384
  • 11
  • 179
  • 318
  • 1
    Specifying a BULK COLLECT INTO ... LIMIT clause is one of the few good reasons to use an explicit cursor. – APC Sep 18 '18 at 18:04