Regression: Insert statement fails with inconsistent datatypes on Oracle 12c driver [SPR-14191]

[spring-projects-issues]

Jurriaan Mous opened SPR-14191 and commented

We get an inconsistent datatypes exception when we upgraded from Spring 4.2.4 to 4.2.5 on an alike statement:

insert into table_name (id,  timestamp_field, clob_field, number_field, date_field, another_number_field) 
          values(:id, current_timestamp, :clob_value, :number_value, :date_value, :another_number)

It works ok if we move the timestamp to the end of the insert. (We chose in the end to downgrade)

(...)
Caused by: java.lang.reflect.UndeclaredThrowableException: null
       at com.sun.proxy.$Proxy203.executeUpdate(Unknown Source)
       at org.springframework.jdbc.core.JdbcTemplate$2.doInPreparedStatement(JdbcTemplate.java:873)
       at org.springframework.jdbc.core.JdbcTemplate$2.doInPreparedStatement(JdbcTemplate.java:866)
       at org.springframework.jdbc.core.JdbcTemplate.execute(JdbcTemplate.java:629)
       at org.springframework.jdbc.core.JdbcTemplate.update(JdbcTemplate.java:866)
       at org.springframework.jdbc.core.JdbcTemplate.update(JdbcTemplate.java:890)
       at org.springframework.jdbc.core.namedparam.NamedParameterJdbcTemplate.update(NamedParameterJdbcTemplate.java:287)
      (.....)
Caused by: java.sql.SQLSyntaxErrorException: ORA-00932: inconsistent datatypes: expected DATE got NUMBER
       at oracle.jdbc.driver.T4CTTIoer.processError(T4CTTIoer.java:450)
       at oracle.jdbc.driver.T4CTTIoer.processError(T4CTTIoer.java:399)
       at oracle.jdbc.driver.T4C8Oall.processError(T4C8Oall.java:1059)
       at oracle.jdbc.driver.T4CTTIfun.receive(T4CTTIfun.java:522)
       at oracle.jdbc.driver.T4CTTIfun.doRPC(T4CTTIfun.java:257)
       at oracle.jdbc.driver.T4C8Oall.doOALL(T4C8Oall.java:587)
       at oracle.jdbc.driver.T4CPreparedStatement.doOall8(T4CPreparedStatement.java:225)
       at oracle.jdbc.driver.T4CPreparedStatement.doOall8(T4CPreparedStatement.java:53)
       at oracle.jdbc.driver.T4CPreparedStatement.executeForRows(T4CPreparedStatement.java:943)
       at oracle.jdbc.driver.OracleStatement.doExecuteWithTimeout(OracleStatement.java:1150)
       at oracle.jdbc.driver.OraclePreparedStatement.executeInternal(OraclePreparedStatement.java:4798)
       at oracle.jdbc.driver.OraclePreparedStatement.executeUpdate(OraclePreparedStatement.java:4875)
       at oracle.jdbc.driver.OraclePreparedStatementWrapper.executeUpdate(OraclePreparedStatementWrapper.java:1361)
       at org.apache.tomcat.jdbc.pool.interceptor.AbstractQueryReport$StatementProxy.invoke(AbstractQueryReport.java:235)

We are using the Oracle Database 12c Release 1 (12.1.0.1) JDBC Driver.

---

Affects: 4.2.5

Attachments:

• OracleJDBCBug.java (6.83 kB)

Issue Links:

• Oracle 12c JDBC driver throws inconsistent exception from getParameterType (affecting setNull calls) [SPR-13825] #18398 Oracle 12c JDBC driver throws inconsistent exception from getParameterType (affecting setNull calls)
• Protect against getParameterType cursor leak with Oracle 12c JDBC driver [SPR-14629] #19196 Protect against getParameterType cursor leak with Oracle 12c JDBC driver

Referenced from: commits 52447ef, 2adbfb6

Backported to: 4.2.8

0 votes, 5 watchers

[spring-projects-issues]

Juergen Hoeller commented

This might be a side effect of #18398... If you have any insight into what's happening there in StatementCreatorUtils before you run into that exception, it would help a lot.

[spring-projects-issues]

Jurriaan Mous commented

We could not find anything specific going wrong debugging in StatementCreatorUtils but we are not experts in what should happen.

!(jdbcDriverName.startsWith("Oracle") && dbmd.getDriverMajorVersion() >= 12)

Maybe an important thing to note is that we run Oracle Database 12c Release 1 (12.1.0.1) JDBC Driver to support the latest Spring version but we talk to an Oracle Database 11.2.0.4.0 instance. We updated because the 11 driver did not support the JDBC 4.1 call in JDBCUtil.java and the application crashed on it. Oracle notes that they support our Oracle DB version with the 12.1.0.x driver.
http://www.oracle.com/technetwork/database/enterprise-edition/jdbc-faq-090281.html#01_02

[spring-projects-issues]

Juergen Hoeller commented

That effect is really odd since our check for Oracle 12 just prevents JDBC getParameterType from getting marked as not supported... which means we'll keep calling getParameterType later on, even if it initially threw an exception towards us for some case. Now you are seeing a data type mismatch when executing the statement, possibly for a null value where we called setNull with a getParameterType-resolved SQL type for that particular parameter... which indicates that the driver itself returned NUMBER from getParameterType but then doesn't accept it on setNull since it really is a DATE field?

If I'm not missing something above, getParameterType seems to be horribly broken on the Oracle 12 driver: sometimes throwing exceptions (which is what #18398 was about), sometimes returning invalid values here now.

So we could check getDatabaseMajorVersion instead of getDriverMajorVersion, assuming that such invalid return values only occur with the Oracle 12 drivers against an older database version... but even then, we'd only mark getParameterType as not supported when encountering the first exception; it might have returned invalid values before already :-(

For the time being, you could set the "spring.jdbc.getParameterType.ignore" property to "true" (as a JVM system property or in a spring.properties file at the Spring library jar level) in order to avoid any getParameterType calls in your scenario. If we can derive this automatically, we'll do it.. but it's still unclear when exactly it does not work on Oracle drivers.

[spring-projects-issues]

Juergen Hoeller commented

FWIW, do you have any indication for whether getParameterType worked against the Oracle 11 driver before? It apparently never returned invalid values for you there, but did it actually work or did it simply always throw an exception?

On Oracle 12, according to #18398, getParameterType generally works; it throws an exception for specific kinds of statements only. This is why we generally keep calling the method even it sometimes throws an exception.

[spring-projects-issues]

Juergen Hoeller commented

Realizing that this is about current_timestamp with NamedParameterJdbcTemplate specifically, I suppose an implicit null value ends up being specified for that statement parameter since it is effectively to be substituted by the database anyway? If this is the setNull call that's failing here, I wonder whether we should skip it altogether... instead of trying to guess its SQL type.

[spring-projects-issues]

Tom Jahncke commented

I ran the same issue, which the root cause is really a bug in the Oracle JDBC driver :(

Here is an excerpt I added to my own internal Jira issue when I encountered the problem:

Working statement: 
"INSERT INTO LOCATION (location_nbr) VALUES (?)" 
getParameterMetaData() 
.getParameterCount() returns a value of 1, which is correct as the statement has one parameter. 

Failing example statement: 
"INSERT INTO LOCATION        (location_nbr) VALUES (?)" 
getParameterMetaData() 
.getParameterCount() returns a value of 61, which represents the count of all the column in the table. Worse the area of parameter is the in the order of the column in the table rather than the parameters leading to incorrect sqlTypes returns from getParameterType(int param) 

Issue 2: 

If the SQL is qualified such as: "INSERT INTO wms_admin.LOCATION (location_nbr) VALUES (?)" instead of "INSERT INTO LOCATION (location_nbr) VALUES (?)" 

The calling method getParameterMetaData().getParameterType(1) errors with the following exception: 

java.sql.SQLFeatureNotSupportedException: Unsupported feature 
at oracle.jdbc.driver.OracleParameterMetaData.checkValidIndex(OracleParameterMetaData.java:176) 
at oracle.jdbc.driver.OracleParameterMetaData.getParameterType(OracleParameterMetaData.java:327) 
at org.springframework.jdbc.core.StatementCreatorUtils.setNull(StatementCreatorUtils.java:262) 

The only workaround is to make sure the SQL used doesn't qualify the table and only has at most one space after the table name in the insert statement. 

This issue was demonstrated with JDBC driver ojdbc7 version 12.1.0.2.0 using Java 8 against a 11.2.0.4.0 Oracle DB 

This means that you are getting inconsistent datatypes when the Oracle driver assumes you are inserting into all the columns rather than the one you specified. I did open a bug with Oracle and they eventually, created a patch for this bug.

I will attached my little sample program that highlights the Oracle bugs (not even using Spring).

[spring-projects-issues]

Tom Jahncke commented

FYI, I think even without the changes in 4.2.5 and Jira issue #18398 you were still at risk of encountering this issue, however, this change may cause you to be more exposed to this oracle jdbc driver bug.

[spring-projects-issues]

Juergen Hoeller commented

Jurriaan Mous, does setting the "spring.jdbc.getParameterType.ignore" property to "true" (as a JVM system property or in a spring.properties file at the Spring library jar level) work for you until Oracle fixes their JDBC driver accordingly? It seems to you were just lucky enough to always run into a getParameterType exception first, silently deactivating further use of it... The only non-fragile solution at this point seems to be to set the property above, even on <4.2.5.

[spring-projects-issues]

Juergen Hoeller commented

Revised for 4.3.3 and 4.2.8 now: StatementCreatorUtils does not use getParameterType on any Oracle driver by default now. If you're on 12c (possibly 12.2 or a custom patch) and would like it to be used, specify spring.jdbc.getParameterType.ignore=false. Since those calls seem to be expensive as well, it is arguably better to only use them when actually needed anyway.

[spring-projects-issues]

Juergen Hoeller commented

It would be great if you could give a recent 4.2.8.BUILD-SNAPSHOT or 4.3.3.BUILD-SNAPSHOT a try... I hope the new defaults (and override options) work for you.