Skip to Main Content

Java Database Connectivity (JDBC)

Announcement

For appeals, questions and feedback about Oracle Forums, please email oracle-forums-moderators_us@oracle.com. Technical questions should be asked in the appropriate category. Thank you!

Interested in getting your voice heard by members of the Developer Marketing team at Oracle? Check out this post for AppDev or this post for AI focus group information.

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
243 views