Oracle Multiple value insert generates invalid query

[marnix riphagen]

Hi jOOQ UG,

We've been using jooq pro 3.18.17 and since upgrading to version 3.19.* we've been having problems with our Oracle db. We've identified that the multiple insert generates a query with "select" in stead of the VALUES ( ) part of the query. This only happens with multiple records in the List.

So this:

dsl.insertInto(<COLUMNS>)
.valuesOfRecords(<VALUES LIST>)

Translates into:

`INSERT INTO <TABLE> (<COLUMNS>) SELECT <VALUES> SELECT <VALUES>`

Sending this to the database results in an error that it can't see FROM where it expected it in the select.

To reiterate, when the list only has 1 record it does translate into a correct query:

`INSERT INTO <TABLE> (<COLUMNS>) VALUES (<VALUES>)`

What is going on? Is this a bug? Are we not seeing something obvious?

Cheers,
Marnix

[Lukas Eder]

Hi Marnix,

Thanks for your message. I assume you're using SQLDialect.ORACLE instead of the versioned dialect. The dialect family always corresponds to the latest supported RDBMS version, and jOOQ 3.19 starts support Oracle 23ai, which no longer requires a FROM clause. So, I suggest using e.g. SQLDialect.ORACLE18C, for example.

I hope this helps,

Lukas

--
You received this message because you are subscribed to the Google Groups "jOOQ User Group" group.
To unsubscribe from this group and stop receiving emails from it, send an email to jooq-user+...@googlegroups.com.
To view this discussion on the web visit https://groups.google.com/d/msgid/jooq-user/bc68ba51-7cd5-4aa4-add1-763fddcd110cn%40googlegroups.com.