Figure out how to return last inserted ID from Oracle DB using reactive-oracle-client

[jsmrcka]

Problem encountered in https://github.com/jsmrcka/quarkus-test-suite/blob/d93d2700b93b5607df1846af2ea6adc97369cdd7/sql-db/vertx-sql/src/main/java/io/quarkus/qe/vertx/sql/services/DbPoolService.java#L96, while trying to implement Oracle-specific save method.

Sample of DDL:

CREATE TABLE airports (
  id            NUMBER GENERATED BY DEFAULT AS IDENTITY PRIMARY KEY,
  iata_code     VARCHAR(100) NOT NULL UNIQUE,
  city          VARCHAR(100) NOT NULL
);

Tried out approach: look for a sequence, which should be automacially generated upon creation of the IDENTITY column, and invoke CURRVAL.
(Inspired by https://www.oracletutorial.com/oracle-sequence/oracle-create-sequence/.)

    protected Uni<Long> saveOracle(String tableName, List<String> fieldsNames, List<Object> fieldsValues) {
        return SqlClientHelper.inTransactionUni(this, tx -> {
            String fields = tableFieldsToString(fieldsNames);
            String values = tableFieldsValuesToString(fieldsValues);

            tx.preparedQuery("INSERT INTO " + getTableName(tableName) + " (" + fields + ") VALUES (" + values + ")").execute();
            return tx.query(
                    "SELECT b.name AS sequence_name FROM sys.idnseq$ c JOIN obj$ a ON c.obj# = a.obj# JOIN obj$ b ON c.seqobj# = b.obj# WHERE a.name = '"
                            + getTableName(tableName) + "'")
                    .execute()
                    .onItem().transform(rows -> rows.iterator().next().getString("sequence_name"))
                    .chain(seqName -> tx.query("SELECT " + seqName + ".CURRVAL l_id FROM DUAL")
                            .execute()
                            .onItem().transform(rows -> rows.iterator().next().getLong("l_id")));
        });
    }

However, this does not work:

io.vertx.core.VertxException: Error : 942, Position : 59, Sql = SELECT b.name AS sequence_name FROM sys.idnseq$ c JOIN sys.obj$ a ON c.obj# = a.obj# JOIN sys.obj$ b ON c.seqobj# = b.obj# WHERE a.name = 'address', OriginalSql = SELECT b.name AS sequence_name FROM sys.idnseq$ c JOIN sys.obj$ a ON c.obj# = a.obj# JOIN sys.obj$ b ON c.seqobj# = b.obj# WHERE a.name = 'address', Error Msg = ORA-00942: table or view does not exist

I tried to adopt other approaches used for other DBs in https://github.com/jsmrcka/quarkus-test-suite/blob/QUARKUS-1407-vertx-sql/sql-db/vertx-sql/src/main/java/io/quarkus/qe/vertx/sql/services/DbPoolService.java, but with no luck - AFAIK, Oracle does not provide anything similar to

INSERT INTO .. RETURNING id

or

SELECT LAST_INSERT_ID()

Any help appreciated.

[jsmrcka]

@Sanne

[jsmrcka]

@tsegismont

[tsegismont]

Would that help? https://vertx.io/docs/vertx-oracle-client/java/#_retrieving_generated_key_values

[jsmrcka]

@tsegismont That did the trick. Thanks an awful lot!
I don't know how I was able to not notice such an obvious and well documented solution, but I did.