0

Does Oracle ~>12 support generated keys using a Merge statement? Some sudo code..

MERGE INTO TARGET_TABLE TRG
USING (SELECT CAST(? AS NUMBER) AS ID FROM DUAL) SRC
ON (TRG.ID = SRC.ID)
WHEN MATCHED THEN UPDATE SET....
WHEN NOT MATCHED THEN
    INSERT(ID....)
    VALUES(MYSEQ.NEXTVAL...)

The prepared statement is set up;

try (PreparedStatement pstmt =
              connection.prepareStatement(
                  loadStatement(sqlName, connection, getClass()), new String[] {ID})) {
...
int inserted = pstmt.executeUpdate();
ResultSet rs = pstmt.getGeneratedKeys();

List<Long> keys = new ArrayList<>(inserted);
while (rs.next) {
    rs.getLong(1);
}
return keys;
...

I have in-memory tests where the connection uses the H2 driver running the very same SQL and PreparedStatment and that returns the generated key just fine, but Oracle does not.

Reviewing the docs it would suggest it does not?

Thanks!

cbm64
  • 1,059
  • 2
  • 12
  • 24
  • The documentation seems pretty self-explanatory. It doesn't suggest: it says flat out that only `insert` statements will return autogenerated keys and other DML will not. – pmdba Jan 09 '23 at 12:16
  • It never hurts to ask the question and have another pair of eyes come to same conclusion before making substantive changes to ones code. Thanks for the reply. – cbm64 Jan 09 '23 at 13:08
  • 1
    I'm afraid it might not work. Since ever Oracle uses its own proprietary extension to SQL: `RETURNING CLAUSE`. This feature is more generic than JDBC's generatedKeys. In order to simulate generaterKeys() feature JDBC statement needs transform entering SQL and also must know in advance generated keys will be needed. Maybe this in not implemented at all in your JDBC drivers. PS: try to wrap your merge statement into anonymous PL/SQL block and you will see whether it works. – ibre5041 Jan 09 '23 at 15:05

0 Answers0