Search code examples
javaoracle-databasejdbctoplink

When are two columns that look the same not the same in oracle?

I am work on a project in oracle 9i. I was having a problem with toplink 10.1.3 loading a particular row in a table. It turns out the jdbc driver that toplink is relying on is acting very funny. Perhaps someone here can help...

I have a table named: crazytable. It has a column: "ver_num number(19) not null default 0". This column was added to the table as part of the original insert some years ago. When I select any record (see below for jdbc connection code) from crazytable and attempt to do an rs.getLong(colIndex), everything works fine. However, if I do a rs.getObject(colIndex), I get a stacktrace:

java.lang.ArrayIndexOutOfBoundsException: 1
    at oracle.sql.NUMBER.toBigDecimal(NUMBER.java:651)
    at oracle.jdbc.dbaccess.DBConversion.NumberBytesToBigDecimal(DBConversion.java:2805)
    at oracle.jdbc.driver.OracleStatement.getBigDecimalValue(OracleStatement.java:4539)
    at oracle.jdbc.driver.OracleStatement.getObjectValue(OracleStatement.java:5666)
    at oracle.jdbc.driver.OracleStatement.getObjectValue(OracleStatement.java:5622)
    at oracle.jdbc.driver.OracleResultSetImpl.getObject(OracleResultSetImpl.java:739)
    at oracle.jdbc.driver.OracleResultSet.getObject(OracleResultSet.java:1470)
    stacktrace truncated to protect my poor code...

I can take another table, lets call it: sanetable, and run this same query against a column with the same name and type "ver_num number(19) not null default 0". And rs.getLong(colIndex) and rs.getObject(colIndex) work just fine.

Neither column is involved in a constraint, or index. I have tried oracle driver 9.2.0.8, 9.2.0.5, 9.2.0.1, even 10.* (which won't work).

Does anyone know anything about what I can do here?

This is my basic connection code. The only difference between the successful calls is the particular table in question:

            Class.forName(oracle.jdbc.OracleDriver.class.getName());
    String url = "jdbc:oracle:thin:@IPADDRESS:PORT:INSTANCE";
        Connection conn = null;
        ResultSet rs = null;
        try {
            conn = DriverManager.getConnection(url, "user","pass");
            PreparedStatement prepareStatement = conn.prepareStatement(
                    "select distinct ver_num " +

                    "FROM [crazytable|sanetable] " 
                    );
            rs = prepareStatement.executeQuery();
            assertNotNull(rs);
            while (rs.next()) {
                ResultSetMetaData md = rs.getMetaData();
                for (int i = 1; i <= md.getColumnCount(); i++) {
                    String key = md.getColumnLabel(i);
                    Object value = rs.getLong(key);
                    System.out.println(key+" : "+value 
                            +" was null: "+rs.wasNull()
                            +" type: "+ rs.getType()
                            +" class: "+ md.getColumnClassName(i));
                }
            }
        } finally {
            if (rs != null) {
                rs.close();
            }
            if (conn != null) {
                conn.close();
            }
        }

edit: The driver can be found on this page: http://www.oracle.com/technology/software/tech/java/sqlj_jdbc/htdocs/jdbc9201.html

Solution

  • Okay I think I figured this out. I was looking through some other questions and noticed there are other oracle type 4 drivers. One of which is DataDirect (http://datadirect.com). I used a trial version of their driver and it was able to return the rs.getObject(intIndex).

    The value was: -1.6777120E-27.

    So rs.getLong() was rounding down to zero, but the BigDecimal was seeing a decimal part and throwing an exception.

    Perhaps this is due to oracle's driver being compiled with jdbc1.4 vs, something newer for datadirect.