I've found a regression when upgrading the server from 23.7.0.25.1 to 23.26.1.0.0 with the following script:
try (Statement s = connection.createStatement()) {
try {
s.executeUpdate("create table t (x xmltype, js clob check (js is json))");
// All of these work:
try (PreparedStatement ps = connection.prepareStatement(
"""
insert into t (js) values (?)
"""
)) {
ps.setString(1, null);
ps.execute();
}
try (PreparedStatement ps = connection.prepareStatement(
"""
insert into t (js) values (?)
"""
)) {
ps.setString(1, "[\"a\", 1]");
ps.execute();
}
try (PreparedStatement ps = connection.prepareStatement(
"""
insert into t (js) values (null), ('["a", 1]')
"""
)) {
ps.execute();
}
// This one fails
try (PreparedStatement ps = connection.prepareStatement(
"""
insert into t (js) values (?), (?)
"""
)) {
ps.setString(1, null);
ps.setString(2, "[\"a\", 1]");
ps.execute();
}
}
finally {
s.executeUpdate("drop table if exists t");
}
}
The problem manifests when:
- Inserting into a table using the multi row
INSERT .. VALUES syntax (it doesn't happen with INSERT .. SELECT .. UNION ALL SELECT .., or with single row inserts)
- The table has an
XMLTYPE column
- The table has an
IS JSON check constraint on a CLOB column
- Bind variables are being used (as opposed to inline values)
I'm reporting this here with the JDBC driver as I couldn't reproduce it with PL/SQL, for example it doesn't reproduce like this:
declare
js1 clob := null;
js2 clob := '["a", 1]';
begin
insert into t (js)
values (js1), (js2);
end;