Search code examples
javasqljooq

JOOQ and Oracle REF CURSOR streaming

JOOQ, by default, wraps REF CURSOR out parameters as Result<Record> in generated code.

In some cases - for large reports, it will be better to have stream of Record - Cursor<Record> or Stream<Record> than fetch all records at once.

Have we any possibility to change generated code for this case or achieve streaming of Record for REF CURSOR in JOOQ by other means?

Solution

  • This is currently (jOOQ 3.12) not possible, I'm afraid. There are some pending feature requests in that area:

    • #4503 Add org.jooq.Cursors extends Iterable<Cursor<Record>>
    • #4472 Add Routine.executeLazy() to allow for fetching org.jooq.Cursors

    Depending on the database product you're using, you could wrap your REF CURSOR returning procedure in a table valued function, though, and fetch the results in an ordinary SELECT statement using jOOQ's ResultQuery.fetchLazy() method. This would work with both Oracle and PostgreSQL.