Skip to Main Content

Java Database Connectivity (JDBC)

Announcement

For appeals, questions and feedback, please email oracle-forums_moderators_us@oracle.com

Cannot correctly bind boolean array of type DBMS_SQL.BOOLEAN_TABLE

Lukas EderNov 16 2023

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]
Comments
Post Details
Added on Nov 16 2023
1 comment
134 views