PostgreSQL: "INSERT ... RETURNING"; Parser ignores RETURNING for Oracle

6 views
Skip to first unread message

spiri...@gmail.com

unread,
Apr 25, 2018, 4:34:37 AM4/25/18
to jOOQ User Group
Hi,

if I put an INSERT RETURNING statement to the jOOQ Parser and convert it to the SQLDialect.ORACLE, the resulting statement comes without the RETURNING.

Is there a way to emulate the RETURNING part with jOOQ?

Greets Mark

Lukas Eder

unread,
Apr 26, 2018, 8:18:21 AM4/26/18
to jooq...@googlegroups.com
Hi Mark,

Currently, the PostgreSQL (and Firebird) specific RETURNING clause is emulated in most SQL dialects through JDBC only, via the Statement.getGeneratedKeys() method. This means that the translated SQL string (when using the parser and re-rendering the SQL in another dialect) is not technically correct, or let's say, complete. It implies that some additional action be taken on the JDBC level. An exception to this is DB2, which supports the SQL standard SELECT .. FROM FINAL TABLE (INSERT ..) syntax.

Oracle also supports INSERT (or UPDATE, DELETE) .. RETURNING, in a similar way as PostgreSQL, but only in the PL/SQL language, not the SQL language. It is not possible to run such a query as an ordinary PreparedStatement, only as a CallableStatement that wraps the INSERT .. RETURNING statement in an anonymous block.

There is a pending feature request to improve this in Oracle:

There's also a request to support SQL Server's OUTPUT clause, which isn't exactly the same thing as RETURNING, though:

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+unsubscribe@googlegroups.com.
For more options, visit https://groups.google.com/d/optout.

Reply all
Reply to author
Forward
0 new messages