Search code examples
javaoraclejdbcojdbc

Why can't a bind variable be used from ojdbc with Oracle JSON_ARRAY() in the presence of FORMAT JSON

In Oracle, the following query:

select
  json_arrayagg(
    json_array(
      (select json_arrayagg(json_array(1) format json) from dual) format json,
      2
    )
    format json
  )
from dual

Produces the following JSON document:

[[[[1]],2]]

When I try to run this query with bind variables from JDBC like this:

try (PreparedStatement s = connection.prepareStatement(
    """
    select
      json_arrayagg(
        json_array(
          (select json_arrayagg(json_array(?) format json) from dual) format json,
          ?
        )
        format json
      )
    from dual
    """
)) {
    s.setInt(1, 1);
    s.setInt(2, 2); // This fails

    try (ResultSet rs = s.executeQuery()) {
        while (rs.next())
            System.out.println(rs.getString(1));
    }
}

Then I'm getting:

Exception in thread "main" java.sql.SQLException: Invalid column index
    at oracle.jdbc.driver.OraclePreparedStatement.setIntInternal(OraclePreparedStatement.java:4956)
    at oracle.jdbc.driver.OraclePreparedStatement.setInt(OraclePreparedStatement.java:4947)
    at oracle.jdbc.driver.OraclePreparedStatementWrapper.setInt(OraclePreparedStatementWrapper.java:202)
    at org.jooq.testscripts.JDBC.main(JDBC.java:50)

I'm using these versions:

  • Database: Oracle Database 21c Express Edition Release 21.0.0.0.0 - Production
  • ojdbc: com.oracle.database.jdbc:ojdbc11:21.5.0.0

Is this a bug? Can it be worked around?

Solution

  • In my opinion, it used to be a bug in ojdbc. It no longer appears in version 23.3.0.23.09. Workarounds for older ojdbc driver versions include:

    Cast the bind variable

    try (PreparedStatement s = connection.prepareStatement(
    """
    select
      json_arrayagg(
        json_array(
          (select json_arrayagg(json_array(?) format json) from dual) format json,
          cast(? as number) -- cast here
        )
        format json
      )
    from dual
    """
)) {
    s.setInt(1, 1);
    s.setInt(2, 2);

    try (ResultSet rs = s.executeQuery()) {
        while (rs.next())
            System.out.println(rs.getString(1));
    }
}

    Remove the FORMAT JSON directive just before the bind parameter marker

    try (PreparedStatement s = connection.prepareStatement(
    """
    select
      json_arrayagg(
        json_array(
          (select json_arrayagg(json_array(?) format json) from dual) /* no format json here */,
          ?
        )
        format json
      )
    from dual
    """
)) {
    s.setInt(1, 1);
    s.setInt(2, 2);

    try (ResultSet rs = s.executeQuery()) {
        while (rs.next())
            System.out.println(rs.getString(1));
    }
}

    Using a dummy expression around the bind variable

    try (PreparedStatement s = connection.prepareStatement(
    """
    select
      json_arrayagg(
        json_array(
          (select json_arrayagg(json_array(?) format json) from dual) format json,
          nvl(null, ?) -- Dummy expression here
        )
        format json
      )
    from dual
    """
)) {
    s.setInt(1, 1);
    s.setInt(2, 2);

    try (ResultSet rs = s.executeQuery()) {
        while (rs.next())
            System.out.println(rs.getString(1));
    }
}

    Interleave a null value, and remove it again with absent on null

    try (PreparedStatement s = connection.prepareStatement(
    """
    select
      json_arrayagg(
        json_array(
          (select json_arrayagg(json_array(?) format json) from dual) format json,
          null, -- This is ignored
          ?
          absent on null
        )
        format json
      )
    from dual
    """
)) {
    s.setInt(1, 1);
    s.setInt(2, 2);

    try (ResultSet rs = s.executeQuery()) {
        while (rs.next())
            System.out.println(rs.getString(1));
    }
}