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

Question

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

Answer 1

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.