oracle-databaseplsqlbulk-collect

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


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,


Solution

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