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

evgeni gelfand

I don't think (I am sure actually) that this is an database server error, this is application error and this is coming from WebSphere.
According to google search results the main advice is to check connectivity between your client (WebSphere I guess) and database server.

1 - 1

Post Details

Added on Nov 16 2023
1 comment
288 views