INSERT SELECT of large CLOB causes ORA-01461 on setString [SPR-12240]

[spring-projects-issues]

Anthony Yeracars opened SPR-12240 and commented

• INSERT INTO table (col1, col2)
• SELECT ?, ?
• FROM DUAL
• fails with ORA-01461 when col1 or col2 is an NCLOB and the associated placeholder parameter is of length > 4000
• while:
• INSERT INTO table (col1, col2)
• VALUES (?, ?)
• works. However, it is unclear that knowing that INSERT VALUES works is relevant.
• (Note: the actual use case is as follows, which means that using INSERT VALUES as a workaround is not satisfactory:)
• INSERT INTO table (col1, col2)
• SELECT ?, ?
• FROM DUAL
• WHERE NOT EXISTS (SELECT * FROM table WHERE col1 = ?)
• (in this case, col1 is an ID column, as per this test case)
• The bug is present in StatementCreatorUtils.setValue(), where Spring JDBC assumes that a CLOB that is small enough can be established with a call to setString() (or,
• if too large, with a call to setObject()). The correct behavior, as demonstrated below, is to call setClob(new StringReader(inValue)) (or, if an NCLOB, then
• setNClob()---in fact, for our use case, we use only NCLOBs). This is consistent with Oracle documentation
• (see http://docs.oracle.com/javase/7/docs/api/java/sql/PreparedStatement.html), which states, for setString(), the following: "Sets the designated parameter to the
• given Java String value. The driver converts this to an SQL VARCHAR or LONGVARCHAR value (depending on the argument's size relative to the driver's limits on VARCHAR
• values) when it sends it to the database." An argument could be made that Oracle's driver should undertake this conversion automatically (i.e., change the String to a
• StringReader or equivalent), although that is a harder road than a Spring update, not to mention that the Oracle's PreparedStatement implementation does not actually
• have enough context to make this determination.
• This test case uses Java 7 and Spring 3.1.0; code inspection shows that the same bug is present in Spring 4.1.0.
• Fails on Oracle drivers 11.2.0.2, 11.2.0.3, 11.2.0.4; server versions 10, 11.1, 11.2

---

Affects: 4.1 GA

Attachments:

• ORA01461.java (9.43 kB)

Issue Links:

• Support for Types.NCLOB in SqlLobValue to simplify use with NamedParameterJdbcTemplate. [SPR-11938] #16555 Support for Types.NCLOB in SqlLobValue to simplify use with NamedParameterJdbcTemplate.
• PreparedStatement#setBlob(int, InputStream) is not supported in DefaultLobCreator [SPR-12265] #16870 PreparedStatement#setBlob(int, InputStream) is not supported in DefaultLobCreator
• 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
• 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)

Referenced from: commits 3908804

[spring-projects-issues]

Juergen Hoeller commented

Oracle's 4000 character limit is well known but a very Oracle-specific pain. The workaround of using setClob works on Oracle but not necessarily on other databases... Have you tried Oracle's "SetBigStringTryClob" setting yet? This is supposed to allow for the use of setString with larger values.

Note that Spring's JDBC template and operation APIs have variants which accept a user-provided PreparedStatementSetter callback which in turn allows for programmatically calling the desired JDBC API methods directly. That's what I would suggest as an alternative approach if needed.

We also have support for a LobHandler abstraction. However, that won't provide much benefit for your particular case. It is rather meant as a helper towards cross-database compatibility - and it is rather historical, dating back to a time when JDBC drivers had no common LOB APIs at all.

Juergen

[spring-projects-issues]

Anthony Yeracars commented

SetBigStringTryClob doesn't work with Oracle 11 drivers, it turns out. (As per http://stackoverflow.com/questions/7817860/does-the-oracle-jdbc-driver-setbigstringtryclob-property-still-work; and I verified it.)

I'm a little surprised setClob() doesn't work on all data managers since it's part of the interface, but I will certainly accept your word. That said, I observe other data-manager-specific code in StatementCreatorUtils.

I had dismissed PreparedStatementSetter while inspecting the code, because SqlUpdate never calls its internal newPreparedStatementSetter() method in this test case, and also does not use the JdbcTemplate.update() method that accepts a PreparedStatementSetter (when I call update(Object ... params)). I have created a workaround that takes advantage of PreparedStatementSetter, although threading it into SqlUpdate is onerous (and fragile).

[spring-projects-issues]

Juergen Hoeller commented

According to that StackOverflow link, setString does work for CLOBs out of the box on Oracle 11, without a need for setting a specific connection property anymore? That seems to suggest that Spring's current use of setString is fine, or am I missing something?

Juergen

[spring-projects-issues]

Anthony Yeracars commented

Please examine and run my test case which demonstrates conclusively that setString() does not work for INSERT ... SELECT ... FROM DUAL, but does work for INSERT ... VALUES, for CLOBs > 4000. I tried very hard to give you complete documentation and a simple test case.

Separately I disagree with your characterization that this should be considered as other than a bug. Oracle clearly documents their interface, which is consistent with the observed behavior. Again, all references can be found in my comments and in the test case itself.

[spring-projects-issues]

Juergen Hoeller commented

I don't mean to deemphasize your test case. I'm just trying to check the situation against the latest Oracle documentation, and this is what the link given on StackOverflow says:

"The setString, setCharacterStream, and setAsciiStream methods use direct binding for data smaller than 32767 bytes in the database character set."
"The setString, setCharacterStream, and setAsciiStream methods use LOB binding for data larger than 32766 bytes in the database character set."

In that context, it's documented that the former "SetBigStringTryClob" connection property isn't needed anymore because the driver automatically chooses LOB bindings now.

This is how Oracle currently documents their drivers in version 11, and this suggests that the 4000 character limit doesn't apply anymore? Seriously, am I missing something?

Juergen

[spring-projects-issues]

Juergen Hoeller commented

There is a documented size limit for setString usage on Oracle 10. However, even that seems to be 32K and not 4000 characters, at least when using the Oracle 11 driver.

In terms of potential changes to Spring's StatementCreatorUtils class, we could switch to setClob usage in case of a specified SQL type 'CLOB'. For an unknown SQL type, setString remains the only feasible choice in case of a String value... but for a dedicated CLOB type, an argument can of course be made for setClob usage... at least as of Spring 4.0 (based on JDK 6+) where we can take JDBC 4.0 and the support for the JDBC LOB APIs for granted.

Juergen

[spring-projects-issues]

Anthony Yeracars commented

So, I didn't try values as large as 32K!!!

The oracle doc for setString() says it will convert the string a VARCHAR or LONGVARCHAR (the latter presumably/apparently when > 4000 bytes); it does not discuss "LOB binding". My (wild) guess is that "LOB binding" refers to the mechanism by which the driver sends the data to the data manager, and not to the datatype that the driver/data manager applies to the parameter. It appears to me that the only way the driver has of understanding the parameter datatype is via which set<type>() call you make; I would say it's not unreasonable for Oracle to say the choices are limited VARCHAR and LONGVARCHAR but not CLOB.

I do admit, by the way, that it is not wholly unreasonable to call this an Oracle bug, but since, from their perspective it is likely they would say the "correct behavior"/"easy workaround" is to use setClob(), it makes more sense to call this a Spring bug.

As I said I now have a klunky/fragile workaround using PreparedStatementSetter: it would (also) be nice if SqlUpdate exposed a (non-final) protected method to generated a PreparedStatementSetter, and actually used it (at least my workaround would be much less fragile).

[spring-projects-issues]

Juergen Hoeller commented

Alright, as of 4.1.1, StatementCreatorUtils uses a setClob/setNClob call with a stream argument in case of SQL type CLOB/NCLOB with a String value exceeding 4000 characters.

This should be fine for other drivers/databases as well since it uses standard JDBC 4.0 API, with some defensiveness added through the length 4000 check (i.e. to keep using setString for most scenarios) as well as a fallback when the driver does not support setClob/setNClob.

We have quite a few related JDBC refinements in 4.1 GA and 4.1.1, so this is a good fit timing-wise. However, due to the changes in interaction with the JDBC driver abstraction and due to this being a vendor/driver-specific problem, it does not suggest itself as a backport candidate.

This will be available in the upcoming 4.1.1 snapshot. Would be great if you could give it a try before the 4.1.1 release on Tuesday...

Juergen

P.S.: As for SqlUpdate, depending on the operation, it uses a PreparedStatementCreator or a PreparedStatementSetter. This is probably why you're not seeing reliable use of the setter variant. In any case, this isn't really meant to be overridden with user code, so I'd rather leave it as-is. For full control there, JdbcTemplate is the better choice.

[spring-projects-issues]

Anthony Yeracars commented

Thank you. Unfortunately we're still running 3.1.0, so I'll have to stick with my JdbcTemplate hack, but that also means I won't have an opportunity to give your fix a proper try-out.