Oracle Vert.x client never return error response when auto-generated keys are enabled with Oracle 23

[michalvavrik]

Questions

No question, thanks.

Version

Vert.x 4.4.4.

Context

I started to use docker.io/gvenzl/oracle-free:23-slim-faststart (which is the default Quarkus Oracle dev services image) and saw that when Oracle exception is thrown and auto-generated keys are enabled, response never comes. What it means: if you set OraclePrepareOptions, response never comes, if you don't pass any options, error response is returned. If you set io.vertx.oracleclient.OraclePrepareOptions#setAutoGeneratedKeys to false, error response arrives.

Do you have a reproducer?

https://github.com/michalvavrik/vertx-sql-oracle-opts-reproducer

Steps to reproduce

1. git clone git@github.com:michalvavrik/vertx-sql-oracle-opts-reproducer.git
2. cd code-with-quarkus
3. quarkus dev
4. when you are done waiting for what never comes, kill the app fuser -k 5005/tcp and pkill -f quarkus

When you run same with Oracle 21, it works: quarkus dev -Dquarkus.datasource.devservices.image-name=docker.io/gvenzl/oracle-xe:21-slim-faststart

Extra

Linux Fedora, OpenJDK 17, I don't use Vert.x directly, I use Quarkus.

[tsegismont]

@michalvavrik to be sure, are you saying that when the database is changed, the bug goes away?

In this case, can you try without Vert.x, just Oracle JDBC + reactive extensions? If the same problem occurs, then a bug report can be sent to Oracle.

[tsegismont]

I was able to reproduce the problem with JDBC + Oracle Reactive Extensions:

import oracle.jdbc.OracleConnection;
import oracle.jdbc.OraclePreparedStatement;
import oracle.jdbc.pool.OracleDataSource;

import java.sql.Connection;
import java.sql.PreparedStatement;
import java.sql.SQLException;
import java.sql.Statement;
import java.util.concurrent.CountDownLatch;
import java.util.concurrent.Flow;

public class ReactiveInsertAndConstraintViolation {

    public static void main(String[] args) throws Exception {
        String url = "jdbc:oracle:thin:@localhost:1521/FREEPDB1";

        OracleDataSource ods = new OracleDataSource();
        ods.setUser("test");
        ods.setPassword("test");
        ods.setURL(url);

        Connection conn = ods.getConnection();

        try (PreparedStatement ps = conn.prepareStatement("DROP TABLE IF EXISTS passenger")) {
            ps.execute();
        }
        try (PreparedStatement ps = conn.prepareStatement("CREATE TABLE passenger (\n" +
                "  id              NUMBER GENERATED BY DEFAULT AS IDENTITY PRIMARY KEY,\n" +
                "  nif             VARCHAR(15) NOT NULL,\n" +
                "  name            VARCHAR(25) NOT NULL,\n" +
                "  last_name       VARCHAR(55) NOT NULL,\n" +
                "  contact_number  VARCHAR(20) NOT NULL,\n" +
                "  created_at      INT NOT NULL,\n" +
                "  updated_at      INT,\n" +
                "  address_id      NUMBER\n" +
                ")")) {
            ps.execute();
        }

        OracleConnection oc = conn.unwrap(OracleConnection.class);

        String sql = "INSERT INTO passenger (" +
                "nif," +
                "name," +
                "last_name," +
                "contact_number," +
                "created_at," +
                "address_id" +
                ") VALUES (" +
                "null," +
                "'Walt'," +
                "'White'," +
                "'+34608554433'," +
                "1691060927694,2)";

        /*
                 Replace the following line with:
                 PreparedStatement ps = oc.prepareStatement(sql);

                 And you will get the expected:
                 java.sql.SQLIntegrityConstraintViolationException: ORA-01400: cannot insert NULL into ("TEST"."PASSENGER"."NIF")

                 You also get the expected exception if your use ojdbc11 21.11.0.0
         */
        PreparedStatement ps = oc.prepareStatement(sql, Statement.RETURN_GENERATED_KEYS);

        OraclePreparedStatement ops = ps.unwrap(OraclePreparedStatement.class);

        CountDownLatch latch = new CountDownLatch(1);

        ops.executeAsyncOracle().subscribe(new Flow.Subscriber<>() {
            @Override
            public void onSubscribe(Flow.Subscription subscription) {
                subscription.request(1);
            }

            @Override
            public void onNext(Boolean item) {
                System.out.println("item = " + item);
            }

            @Override
            public void onError(Throwable throwable) {
                throwable.printStackTrace();
                try {
                    conn.close();
                } catch (SQLException e) {
                }
                latch.countDown();
            }

            @Override
            public void onComplete() {
                System.out.println("Done");
                try {
                    conn.close();
                } catch (SQLException e) {
                }
                latch.countDown();
            }
        });

        latch.await();
    }
}

POM file:

<?xml version="1.0" encoding="UTF-8"?>
<project xmlns="http://maven.apache.org/POM/4.0.0"
         xmlns:xsi="http://www.w3.org/2001/XMLSchema-instance"
         xsi:schemaLocation="http://maven.apache.org/POM/4.0.0 http://maven.apache.org/xsd/maven-4.0.0.xsd">
    <modelVersion>4.0.0</modelVersion>

    <groupId>org.example</groupId>
    <artifactId>jdbc-reactive-oracle</artifactId>
    <version>1.0-SNAPSHOT</version>

    <properties>
        <maven.compiler.source>11</maven.compiler.source>
        <maven.compiler.target>11</maven.compiler.target>
        <project.build.sourceEncoding>UTF-8</project.build.sourceEncoding>
    </properties>

    <dependencies>
        <dependency>
            <groupId>com.oracle.database.jdbc</groupId>
            <artifactId>ojdbc11</artifactId>
            <version>21.11.0.0</version>
        </dependency>
    </dependencies>

</project>

We must report this to Oracle

[michalvavrik]

sorry @tsegismont for not answering, I was on PTO and then trying to catch up with stuff.

We must report this to Oracle

Well, yes I understand it ultimately needs to be fixed in Oracle DB JDBC, but considering I'm not using it directly but via Eclipse Vert.X, this seems like a right place at least to have it as known issue.

[loiclefevre]

A bug has been created. ETA is 23.4.0.0 version of the JDBC driver.
Thanks for reporting!

Eventually, any 23c FREE related issue (being related to the database or clients, hence including the JDBC driver) can be reported here as well: https://forums.oracle.com/ords/apexds/domain/dev-community/category/oracle-database-free

[tsegismont]

Thank you @loiclefevre for the update.

Eventually, any 23c FREE related issue (being related to the database or clients, hence including the JDBC driver) can be reported here as well: https://forums.oracle.com/ords/apexds/domain/dev-community/category/oracle-database-free

I will report any problem that may be found later to this forum 👍