The JDBC 11g driver and the JDBC 10g driver differ in handling timestamp variables.
Create a ts timestamp variable in Java
java.sql.Timestamp ts = new java.sql.Timestamp(date.getTime());
If you compare this variable with the Date field in the database, for example
CREATE TABLE TEST_DATE (DATE_FIELD Date)
SELECT COUNT(1) FROM TEST_DATE WHERE DATE_FIELD >= ?
Ts in java declared at the beginning of binding are binding variables
Driven by 10g JDBC, Oracle oracle will truncate the millisecond part of the java timestamp variable and compare it, that is to say
2010-09-15 16:00:00 (date column) >= 2010-09-15 16:00:00.00100 (timestamp value)
But under the JDBC 11g drive, Oracle will convert the Date column to timestamp and add the millisecond part to 0, which is
2010-09-15 16:00:00 (date column) will be converted to 2010-09-15 16:00:00.00000 (timestamp value)
In this case
2010-09-15 16:00:00.00000 (converted value) >= 2010-09-15 16:00:00.00100 (timestamp value) does not hold
When inserting the value, if the Timestamp java variable is used, the processing of 10g and 11g is to cut off the millisecond part.
The first problem is that after upgrading the 10g JDBC driver to 11g, the data results will be different. E.g:
insert into TEST_DATE (DATE_FIELD) values (?); — bind variable timestamp value ts
select count(*）from TEST_DATE where DATE_FIELD=?; — bind variable timestamp value ts
It will always return 1 under 10g JDBC driver, and it is likely to return 0 under 11g JDBC driver.
Another problem is that this conversion will cause the index on the DATE column to be unavailable, similar to the conversion between Number and Varchar2. You can see the INTERNAL_FUNCTION function in the results of the explain plan.
I sent a tar to Oracle. Oracle support does not think this is a bug but thinks it is a correction to the previous processing method and does not provide backward-compatible parameters (such as the previous V8Compatible).
There is currently no other way except to modify the program code. But this is a nightmare for a lot of code search and modification.