I'm using the latest 23.3 free database from here: https://hub.docker.com/r/gvenzl/oracle-free, and com.oracle.database.jdbc:ojdbc11:23.3.0.23.09
.
Consider this program:
try (CallableStatement c = connection.prepareCall(
"""
DECLARE
n DBMS_SQL.NUMBER_TABLE;
a DBMS_SQL.BOOLEAN_TABLE;
BEGIN
n := ?;
? := n;
a := ?;
? := a;
END;
"""
)) {
c.setArray(1, connection
.unwrap(OracleConnection.class)
.createARRAY("DBMS_SQL.NUMBER_TABLE", new Object[] { 1, 2 })
);
c.setArray(3, connection
.unwrap(OracleConnection.class)
.createARRAY("DBMS_SQL.BOOLEAN_TABLE", new Object[] { true, false })
);
c.registerOutParameter(2, Types.ARRAY, "DBMS_SQL.NUMBER_TABLE");
c.registerOutParameter(4, Types.ARRAY, "DBMS_SQL.BOOLEAN_TABLE");
c.executeUpdate();
System.out.println(Arrays.asList((Object[]) c.getArray(2).getArray()));
System.out.println(Arrays.asList((Object[]) c.getArray(4).getArray()));
}
The expected output is:
[1, 2]
[true, false]
But it's printing:
[1, 2]
[-1056806629, -2147325669]
It appears this has already been this way before, i.e. in version 21.11.0.0
of the driver.
The problem seems to be caused by the binding of the variable, not the fetching. I.e. when using this anonymous block:
DECLARE
n DBMS_SQL.NUMBER_TABLE;
a DBMS_SQL.BOOLEAN_TABLE;
BEGIN
n := ?;
? := n;
a := ?;
a(3) := TRUE;
a(4) := FALSE;
? := a;
END;
Then, the additional values are fetched as more meaningful numbers 1
and 0
:
[1, 2]
[-1056833536, -2147352576, 1, 0]