getBoolean in 12c JDBC Driver and java.sql.SQLException: Fail to convert to internal representation

There's no Boolean data type in Oracle database. When getBoolean method is called on result set object internal conversion takes place. According to Oracle documentation this internal conversion is only applicable to numeric columns. For any other type of column the method would throw a NumberFormatException. This is the same case documented in previous version (10gR2) as well.
However in practice this hasn't been the case. For example a test table containing following column types and data should raise an exception as only the first column would be compatible with what Oracle documentation says (It is not all that uncommon to find applications which have used char(1) or varchar2(1) to denote boolean).

create table getboolean(a number, b char(1), c varchar2(1));
insert into getboolean values (1,'1','1');
insert into getboolean values (0,'0','0');
insert into getboolean values (2,'2','2');

But if a JDBC driver of 11.2.0.3 or 11.2.0.4 (as in this post) is used, boolean representation of all rows are returned without any error( Java code available at the end of the post). In this case values of 1 or above are returned as true and 0 as false.

Driver Version 11.2.0.4.0

true true true
false false false
true true true

However if a 12.1 or 12.2 driver is used only first two rows are returned and an exception occurs on the third row.

Driver Version 12.1.0.2.0

true true true
false false false

Exception in thread "main" java.sql.SQLException: Fail to convert to internal representation
 at oracle.jdbc.driver.CharCommonAccessor.getBoolean(CharCommonAccessor.java:183)
 at oracle.jdbc.driver.GeneratedStatement.getBoolean(GeneratedStatement.java:129)
 at oracle.jdbc.driver.GeneratedScrollableResultSet.getBoolean(GeneratedScrollableResultSet.java:232)
 
Driver Version 12.2.0.1.0

true true true
false false false

Exception in thread "main" java.sql.SQLException: Fail to convert to internal representation
 at oracle.jdbc.driver.CharCommonAccessor.getBoolean(CharCommonAccessor.java:193)
 at oracle.jdbc.driver.GeneratedStatement.getBoolean(GeneratedStatement.java:125)
 at oracle.jdbc.driver.GeneratedScrollableResultSet.getBoolean(GeneratedScrollableResultSet.java:134)

With a 12c driver (both 12.1 and 12.2) if only the first column (number type column) is selected then there's no exception

Driver Version 12.1.0.2.0

true
false
true

If second or thrid column is selected only first two rows are returned and an exception is raised when retrieving the third row.

Driver Version 12.1.0.2.0

true
false
Exception in thread "main" java.sql.SQLException: Fail to convert to internal representation

This is a change in behavior compared to 11.2 driver. 12c drivers no longer works for non-numeric columns if the values is greater than 1. This is somewhat expected behavior as per documentation, though the error thrown is not the same. On the other hand 12c drivers still work for non-numeric columns if values are 0 or 1 (same behavior as 11.2 drivers).
This change in behavior need to be considered when JDBC drivers are upgraded to 12c.

public class BooleanTest {

    public static void main(String[] args) throws SQLException {

        PoolDataSource ds = PoolDataSourceFactory.getPoolDataSource();
        ds.setConnectionFactoryClassName("oracle.jdbc.pool.OracleDataSource");
        ds.setUser("asanga");
        ds.setPassword("asa");
        ds.setURL("jdbc:oracle:thin:@192.168.0.66:1521:ent11g2");

        Connection con = ds.getConnection();

        DatabaseMetaData meta = con.getMetaData();

        System.out.println("Driver Name " + meta.getDriverName());
        System.out.println("Driver Version " + meta.getDriverVersion());
        System.out.println("Driver Major Version " + meta.getDriverMajorVersion());
        System.out.println("Driver Minor Version " + meta.getDriverMinorVersion());
        System.out.println("Database Major Version " + meta.getDatabaseMajorVersion());
        System.out.println("Database Minor Version " + meta.getDatabaseMinorVersion());

        PreparedStatement pr = con.prepareStatement("select * from getboolean");
        ResultSet rs = pr.executeQuery();
        while (rs.next()) {

            System.out.println(rs.getBoolean(1) + " " + rs.getBoolean(2) + " " + rs.getBoolean(3));
//            System.out.println(rs.getBoolean(1)); // works fine for 12c 
//            System.out.println(rs.getBoolean(2)); // causes issue in 12c for values > 1
        }
        rs.close();
        pr.close();
        con.close();
    }

}

Related Posts
java.sql.SQLException: Could not commit with auto-commit set on When Using 12c JDBC Driver
Change in 12c JDBC Behavior - setDate & getDate Does Not Truncate Timestamp