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

user_521799 0
Viewed 262

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?

ojdbc jdbc oracle java
1 Answers

In my opinion, it's a bug in ojdbc. Workarounds 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));
    }
}
user_5217990
Related