Clever Geek Handbook

DELETE .. RETURNING rowid in JOOQ doesn't work

I have an oracle database and I am trying to delete a record based on customer number, with a query returning the rowid of the deleted record. I get the following exception when executing the request: java.lang.IllegalArgumentException: The field (rowid) is not contained in the row. If I try to return a different field (like the client_number field) instead of the rowid, the query works fine.

The request I'm trying to execute looks like this:

ClientDetails clt = CLIENT_DETAILS.as("clt");

ClientDetailsRecord result = context.deleteFrom(clt)
            .where(clt.CLIENT_NUMBER.equal(clientNumber))
            .returning(rowid())
            .fetchOne();

Is this a Jooq limitation or am I doing it wrong?

+3


source to share


1 answer


This is a known (and unfortunate) limitation in jOOQ 3.x that can only return the declared columns of your table CLIENT_DETAILS

, no "system" columns such as ROWID

, or any expressions. Relevant feature request to fix this: https://github.com/jOOQ/jOOQ/issues/5622

You can work around this limitation by creating your own table CLIENT_DETAILS

that includes a "synthetic" column ROWID

, for example. by:



  • Type extensionCustomTable

  • Extending the code generator by adding a column ROWID

    to the table CLIENT_DETAILS

    (be careful that this can have unwanted side effects, for example when called UpdatableRecord.store()

    )
+2


source






More articles:


All Articles