Oracle 12c JDBC driver throws inconsistent exception from getParameterType (affecting setNull calls) [SPR-13825]

[spring-projects-issues]

Tom Jahncke opened SPR-13825 and commented

This relates to: #15726

Using the oracle driver (ojdbc7 version 12.1.0.2.0) I ran across the following issue (I opened a support case with Oracle on the root cause of the issue. SR 3-11927085951)

Normally the Oracle driver works fine with following code:

sqlTypeToUse = ps.getParameterMetaData().getParameterType(paramIndex); (from setNull in StatementCreatorUtils line 262)  

However, it is possible for a statement to cause Oracle to throw an exception than then all statements going forward no longer use the JDBC 3.0 features to resolve the sql data type for null values.

I understand why driversWithNoSupportForGetParameterType exists and how it helps performance. However, since it is black or white once an exception is thrown the setNull never tries to use the JDBC 3.0 features again (until the app is restarted).

I can think of a few hackish ways to address this, but I am hoping you can come with a solid solution that is solid (My hackish ideas are along the lines or allowing a driver name to be injected that doesn't get added to the global set of drivers not supported.)

FYI, just by having an insert statement with a qualified table name causes the following exception with the oracle JDBC driver. (Without qualification no 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)

---

Affects: 4.2.4

Attachments:

• Oralce-JDBC-Bug 22983939.pdf (186.38 kB)

Issue Links:

• PreparedStatement#getParameterMetaData() calls may trigger unwanted side effects [SPR-11386] #16013 PreparedStatement#getParameterMetaData() calls may trigger unwanted side effects
• Regression in handling of String passed as Types.OTHER to JdbcTemplate [SPR-12890] #17488 Regression in handling of String passed as Types.OTHER to JdbcTemplate
• performancedrop due to repeated JDBC 3.0 getParameterType calls in setNull in StatementCreatorUtils [SPR-11100] #15726 performancedrop due to repeated JDBC 3.0 getParameterType calls in setNull in StatementCreatorUtils
• INSERT SELECT of large CLOB causes ORA-01461 on setString [SPR-12240] #16854 INSERT SELECT of large CLOB causes ORA-01461 on setString
• Java boolean is not handled correctly when used with Oracle JDBC driver [SPR-14116] #18688 Java boolean is not handled correctly when used with Oracle JDBC driver
• Regression: Insert statement fails with inconsistent datatypes on Oracle 12c driver [SPR-14191] #18764 Regression: Insert statement fails with inconsistent datatypes on Oracle 12c driver
• Avoid repeated getParameterType calls for setNull with Oracle 12c driver [SPR-14574] #19143 Avoid repeated getParameterType calls for setNull with Oracle 12c driver
• 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 e48ec4f, e1bdf55

0 votes, 6 watchers

[spring-projects-issues]

Juergen Hoeller commented

I suppose we will go with explicit detection of compliant Oracle JDBC driver versions where we can reliably assume that they do support getParameterType, so a SQLFeatureNotSupportedException can be considered as a temporary exception that does not indicate general lack of support.

That said, the Oracle driver's choice of exception is really misleading there. It means to say "not supported for this particular statement" but chooses to throw an exception that indicates general lack of a particular JDBC feature... Anyway, even if that exception remains, we can certainly work around it.

Juergen

[spring-projects-issues]

Tom Jahncke commented

I agree that Oracle choice is very misleading and that getParameterType should work even when the table name is qualified. Thank you for looking into this so quickly!

[spring-projects-issues]

Juergen Hoeller commented

Addressed through a getDriverMajorVersion check for 12 and above on the Oracle JDBC driver. I'll backport this to 4.2.x right away; please give the upcoming 4.2.5.BUILD-SNAPSHOT a try and let me know whether it works for you in your actual Oracle scenario!

Juergen

[spring-projects-issues]

Tom Jahncke commented

I tested the fix with 4.2.5.BUILD-SNAPSHOT and it worked well. Thank you again for the quick turn around on this fix!!

[spring-projects-issues]

Juergen Hoeller commented

Alright, thanks for the immediate feedback!

Juergen

[spring-projects-issues]

Juergen Hoeller commented

While this has been backported to 4.2.x, we won't backport it to the 3.2.x line since many other recent JDBC refinements haven't been backported there either. Generally, please upgrade to 4.2+ for proper Oracle 12 support.

[spring-projects-issues]

Juergen Hoeller commented

Tom.Jahncke@gfs.com, since we have a side effect of this reported in #18764, could you please clarify whether you are using the Oracle 12 JDBC driver against an actual Oracle 12 database there?

[spring-projects-issues]

Tom Jahncke commented

This was against a 11.2.0.4 Oralce database with the Oracle 12 JDBC driver. FYI, Oracle did eventually acknowledge this bug and created a patch for the underlying bug, however, that won't be part of their standard JDBC driver until the next official release of their driver.

[spring-projects-issues]

Juergen Hoeller commented

Alright, thanks for the clarification... It looks like the Oracle 12 driver not only sometimes throws exceptions against the Oracle 11 database but even sometimes returns invalid values (according to #18764). We'll see what we can do to workaround that part.

[spring-projects-issues]

Tom Jahncke commented

Oracle JDBC has yet another bug :( This results in a cursor leak when the getParameterMetaData method throws an exception. The cursor leak in my situation was noticed when the program was getting java.sql.SQLException: ORA-01000: maximum open cursors exceeded.

Oracle does have a patch for this as for April 2016. You can look up oracle doc: "Cursor Leak / ORA-1000 From PreparedStatement.getMetaData() Method On Malformed Statements (Doc ID 2124104.1)" at support.oracle.com for more information.

I am commenting here for visibility sake, as the Oracle docs don't show up in a Google search and I spend an entire day dealing with this. Hopefully, this saves someone else time.

[spring-projects-issues]

Tom Jahncke commented

@Juergen Hoeller, based on what I found and represented in the previous comment I am wondering if this feature needs to rolled back (or replace with a property that can be turned on to always attempt to use the meta data from the prepared statement.) I am concerned when users of Spring upgrade to 4.2.5+ they will have the potential for a cursor leak which could be a ticking time bomb for the database session.

[spring-projects-issues]

Juergen Hoeller commented

We have "spring.jdbc.getParameterType.ignore" already... Do you see a need for something beyond that?

The behavior was a bit weird before when we would only stop trying getParameterType once one of Oracle 12c's exceptions gets thrown from it... so we might use it for hours and then suddenly stop using it. The revised behavior that we currently have is more predictable from that perspective.

[spring-projects-issues]

Tom Jahncke commented

I agree with what you stated. The risk / challenge is that every time getParameterMetaData throws an exception Oracle is leaving an open cursor behind, shame Oracle. Oracle has been aware of this bug since 2014 and they have a one off patch you can apply, but you have to seek this out. This will not be transparent to users and may not even be noticed until application gets to production. What makes this even worse is Oracle seems to have lots of situations where the SQL runs fine, but the getParameterMetaData throws an exception :(

I am aware of spring.jdbc.getParameterType.ignore, what i was suggesting was a parameter like spring.jdbc.getParameterType.always (default to false) and then remove the Oracle driver / version check in StatementCreatorUtils and instead check to see if this variable is set.

By the way, I am content leaving the code the way it is as it is working for me because I have applied the patch from Oracle. I am just attempting to consider the larger community. I leave the decision in your very capable hands.

[spring-projects-issues]

Felix Barnsteiner commented

After upgrading to the latest spring version I also ran into the ticking time bomb of java.sql.SQLException: ORA-01000 where after a while my application would not be able to respond to requests anymore. Setting spring.jdbc.getParameterType.ignore helped, but it took me a while to figure out what was going on. I'd suggest to revert or rethink this change. Just for the record, I'm using oracle driver 12.1.0.2 with Oracle 11.2.0.4.0.

[spring-projects-issues]

Gopi Popuri commented

We are also facing java.sql.SQLException: ORA-01000 after upgrading the latest version.. Our configuration also same as Felix..

[spring-projects-issues]

Juergen Hoeller commented

I've created #19196 as a follow-up to this issue, considering more defensive - but still consistent - default behavior when running against Oracle 12c. It seems the only way out is to deactivate all getParameterType access by default against that driver version, opting in through explicitly setting spring.jdbc.getParameterType.ignore to false instead (which would then consistently use getParameterType even if sometimes an exception gets thrown, as per the intent of the original issue here).

[spring-projects-issues]

Juergen Hoeller commented

From all reports above and in #18764, it seems that this only occurs with the Oracle 12.x JDBC driver against an Oracle 11.x database system. Has anybody got insight into whether this happens against an Oracle 12.x database as well? Tom Jahncke, does the Oracle support ticket for the patch mention affected database versions?

[spring-projects-issues]

Juergen Hoeller commented

A similar kind of leak for getTables seems to happen against both database versions: https://community.oracle.com/thread/3774048

[spring-projects-issues]

Tom Jahncke commented

The Oracle docs on the bug are related to the JDBC driver affects RDMBS version 12.1.0.2 (in addition to the issues we are seeing with 11.2.0.4 RDMBS versions.)

[spring-projects-issues]

Tom Jahncke commented

Here is the Oracle write up on the bug. They do have a patch for this, but you have to contact Oracle directly :( They only make new version of the JDBC driver in conjunction with a new version of the database.

[spring-projects-issues]

Juergen Hoeller commented

So they're only going to ship that fix once an Oracle 13 JDBC driver gets released? Which might take a further year or two? sigh

With the recent reports here and considering that #18764 is still open, it looks like we have no other choice than to ignore getParameterType on Oracle 12c by default. I intend to do that consistently though, not even trying getParameterType unless we get an explicit opt-in.

It's a shame that such an old JDBC feature - dating back to JDBC 3.0 in JDK 1.4, making it 14 years old (!) - still does not work properly today :-(

[spring-projects-issues]

Tom Jahncke commented

I think they would release the patch when the release 12.2 (which was suppose to be this summer, but I believe is delayed.)

I fully agree it is such a shame and makes me even more disappointed in Oracle.

I agree with your proposed approach.

[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

Tom Jahncke, all, this is available in the latest 4.3.3.BUILD-SNAPSHOT and 4.2.8.BUILD-SNAPSHOT now. Please give it a try, both with no custom setting at all (which should restore pre-4.2.5 behavior in a more consistent form through never attempting a getParameterType call on Oracle by default) and with a spring.jdbc.getParameterType.ignore=false override (which should reveal the leak again - or work fine if you got a patched driver).

[spring-projects-issues]

Kuassi Mensah commented

Full disclosure: I work for Oracle.

Regarding the following comment
They only make new version of the JDBC driver in conjunction with a new version of the database.@Mention someone by typing their name...
I'd like to say that this is untrue, we furnish patches for bugs all the time for customers with a support contract however, if the expectation is to make these patches available on OTN, that's not the purpose of OTN (iow, it is not the place to get bug fixes).

[spring-projects-issues]

Tom Jahncke commented

@Kuassi Mensah, what I was trying to indicate that when a bug is fixed the standard oracle JDBC driver jar does get the patch until the next major release (such as 12.2). So, customers of Oracle have to encounter the bug first hand and they look for a patch if one exists for their issue.

[spring-projects-issues]

Kuassi Mensah commented

Bug fixes are also picked up by intermediate patchsets (4th digit) e.g., 12.1.0.2.

[spring-projects-issues]

Tom Jahncke commented

@Kuassi, are bugs release for the JDBC Oracle driver independently of the patchset for the Oracle database? My disappointment with Oracle is that I encounter this issue which they had a patch for that was was released months before I encounter the issue. So, I had to encounter the pain just to find out there was a patch I could request and apply. This took several hours (probably days worth of wasted time.) There are obviously others encountering the same Oracle JDBC driver bug and wasting time when there is a known fix.

@Jurergen, if this is way off topic feel free to tell us to take up this discussion off line.

[spring-projects-issues]

Kuassi Mensah commented

Yes, bug fixes are posted indepedently of patch sets and available in a repository for supported customers.

I understand your frustration; your experience is not the way things work. When you submit a technical assistance request (a.k.a. TAR), the support engineer should, after investigation, tell you whether this is a bug or not; if so, whether ther already is a fix or not; if so, point you to the fix. THis should only take the time o investigate the TAR and the time to pull the fix and apply.

Best, Kuassi