Search code examples
javaoracle

What does Oracle's ResultSet return on NUMBER columns?


What if a column is of type NUMBER, and I call getBoolean() on the ResultSet? What does it return?

The javadoc for the interface doesn't list the NUMBER type

java.sql.ResultSet

If the designated column has a datatype of CHAR or VARCHAR and contains a "0" or has a datatype of BIT, TINYINT, SMALLINT, INTEGER or BIGINT and contains a 0, a value of false is returned. If the designated column has a datatype of CHAR or VARCHAR and contains a "1" or has a datatype of BIT, TINYINT, SMALLINT, INTEGER or BIGINT and contains a 1, a value of true is returned

The doc for Oracle's extension doesn't elaborate

Java 8

JDBC Driver version com.oracle.database.jdbc:ojdbc6:11.2.0.4

Oracle Database 11g Enterprise Edition Release 11.2.0.3.0 - 64bit Production

UPD

Is this the thing? IDE doesn't download the sources for some reason so I figure it's as far as we can go. I don't get what this code does

// oracle.jdbc.driver.NumberCommonAccessor#getBoolean
    boolean getBoolean(int var1) throws SQLException {
        boolean var2 = false;
        if (this.rowSpaceIndicator == null) {
            SQLException var6 = DatabaseError.createSqlException(this.getConnectionDuringExceptionHandling(), 21);
            var6.fillInStackTrace();
            throw var6;
        } else {
            if (this.rowSpaceIndicator[this.indicatorIndex + var1] != -1) {
                byte[] var3 = this.rowSpaceByte;
                int var4 = this.columnIndex + this.byteLength * var1 + 1;
                byte var5 = var3[var4 - 1];
                var2 = var5 != 1 || var3[var4] != -128;
            }

            return var2;
        }
    }

Solution

  • From the Oracle 11g JDBC Developer's Guide: 11 Accessing and Manipulating Oracle Data

    getBoolean

    Because there is no BOOLEAN database type, when you use getBoolean a data type conversion always occurs. The getBoolean method is supported only for numeric columns. When applied to these columns, getBoolean interprets any zero value as false and any other value as true. When applied to any other sort of column, getBoolean raises the exception java.lang.NumberFormatException.

    The corresponding Oracle 23ai JDBC Developer's Guide: 12 Accessing and Manipulating Oracle Data was updated to include the BOOLEAN column type, otherwise, for numeric columns, nothing changed.

    Note: The best way may be to test the behaviour using your driver (and then add an integration test to your test suite so that you can track any changes to the behaviour if you upgrade drivers). Else, upgrade to a modern, supported database version.