Oracle 10g JDBC driver and BigDecimal.toString()

I would like to share an issue with you regarding Oracle 10g JDBC driver and migration to Java 5.

Recently we encountered a production bug where a client got some extra money on his/her account. The bug appeared with Oracle JDBC driver (ojdbc14.jar) version 10.1.0.4 once we migrated the application to Java 5 from Java 1.4.

In order to safe the precision in Java it is the common practice to use BigDecimals. Oracle driver, while binding the objects to SQL types, is calling BigDecimal.toString() method. In Java 5, the method was altered in order to support JSR-13:Decimal Arithmetic Enhancement standard. This issue exists in Sun bug database

Here's a piece of code to reproduce this problem:

-- table for the test
create table test_table ( value number )

// java code to reproduce the behaviour
import java.math.BigDecimal;
import java.sql.Connection;
import java.sql.DriverManager;
import java.sql.PreparedStatement;
public class HelloFromOracle {
public static void main(String[] args) throws Exception {
Class.forName("oracle.jdbc.OracleDriver");
Connection connection = DriverManager.getConnection("jdbc:oracle:thin:user/passwd@DB:1521:SID");
String query = "insert into test_table values (?)";
PreparedStatement stmt = connection.prepareStatement(query);
BigDecimal bd = new BigDecimal("0.000000000000001");
stmt.setBigDecimal(1, bd);
stmt.execute();
stmt.close();
connection.close();
}
}

Now, with Oracle driver v 10.1.0.4 the value in database will be:
> select * from test_table
VALUE
-------------------------
5115

... And with Oracle driver v 10.1.0.5
> select * from test_table
VALUE
-------------------------
0.000000000000001


So my message is to replace the Oracle driver 10.1.0.4 to 10.1.0.5, especially with migration to Java 5 from Java 1.4. What is interesting is that oracle driver 8.1.7.4 doesn't suffer from this problem.