Spring JDBC lob insertion fails: Invalid column type

Question

The code:

                jdbcTemplate.update("MERGE INTO app_role_data x USING (select ? name, ? xml FROM dual) d ON (x.app_name = d.name) WHEN MATCHED THEN UPDATE SET x.xml_blob = d.xml WHEN NOT MATCHED THEN INSERT(app_name, xml_blob) VALUES(d.name, d.xml)",
                    new AbstractLobCreatingPreparedStatementCallback(lobHandler) {
                            protected void setValues(PreparedStatement ps, LobCreator lobCreator) throws SQLException {
                                    ps.setString(1, appName);
                                    lobCreator.setClobAsString(ps, 2, xmlBlob);
                            }
                    });

lobHandler is an instance of OracleLobHandler that's injected in the context.

The exception:

Caused by: java.sql.SQLException: Invalid column type
    at oracle.jdbc.driver.DatabaseError.throwSqlException(DatabaseError.java:112)
    at oracle.jdbc.driver.DatabaseError.throwSqlException(DatabaseError.java:146)
    at oracle.jdbc.driver.DatabaseError.throwSqlException(DatabaseError.java:208)
    at oracle.jdbc.driver.OraclePreparedStatement.setObjectCritical(OraclePreparedStatement.java:9231)
    at oracle.jdbc.driver.OraclePreparedStatement.setObjectInternal(OraclePreparedStatement.java:8812)
    at oracle.jdbc.driver.OraclePreparedStatement.setObjectInternal(OraclePreparedStatement.java:9534)
    at oracle.jdbc.driver.OraclePreparedStatement.setObject(OraclePreparedStatement.java:9517)
    at org.springframework.jdbc.core.StatementCreatorUtils.setValue(StatementCreatorUtils.java:351)
    at org.springframework.jdbc.core.StatementCreatorUtils.setParameterValueInternal(StatementCreatorUtils.java:216)
    at org.springframework.jdbc.core.StatementCreatorUtils.setParameterValue(StatementCreatorUtils.java:144)
    at org.springframework.jdbc.core.ArgPreparedStatementSetter.doSetValue(ArgPreparedStatementSetter.java:65)
    at org.springframework.jdbc.core.ArgPreparedStatementSetter.setValues(ArgPreparedStatementSetter.java:46)
    at org.springframework.jdbc.core.JdbcTemplate$2.doInPreparedStatement(JdbcTemplate.java:815)
    at org.springframework.jdbc.core.JdbcTemplate$2.doInPreparedStatement(JdbcTemplate.java:1)
    at org.springframework.jdbc.core.JdbcTemplate.execute(JdbcTemplate.java:586)

This is Spring framework 3.0.5.RELEASE.

Our oracle driver dependency is

      <dependency>
        <groupId>com.oracle</groupId>
        <artifactId>ojdbc14</artifactId>
        <version>10.2.0.3.0</version>
      </dependency>

The table:

SQL> desc app_role_data
 Name                      Null?    Type
 ----------------------------------------- -------- ----------------------------
 APP_NAME                       VARCHAR2(64)
 XML_BLOB                       CLOB

I'm not even sure what other information I can add, but I'll be happy to do so if I've left something out.

This fails the same way with the query simply being "INSERT INTO app_role_data(app_name, xml_blob) VALUES (?,?)" — nsayer, Aug 13 '12 at 20:33
Note that this fails with Spring 3.1.1.RELEASE as well. I seriously can't believe that I am the only person in the entire universe that is unable to insert a CLOB into an Oracle 11g database with Spring JDBC 3.x. — nsayer, Aug 14 '12 at 17:40

score 2 · Accepted Answer · answered Aug 14 '12 at 19:41

2

Turns out, execute() takes a PreparedStatementCallback, while update() takes a PreparedStatementSetter. But both have overloads that take Object, so the compiler doesn't complain.

Grumble. That's a day of my life I'll never get back.

answered Aug 14 '12 at 19:41

nsayer

16,925
3
33
51

Anjan Biswas · Answer 2 · 2012-08-13T20:35:45.680

0

I am being a little skeptical about the column names here

Can you try something like below in your MERGE

select ? as name, ? as xml FROM dual

But I am skeptical of that too since the way you can have dynamic column names is by creating a dynamic sql and executing it with EXECUTE IMMEDIATE.

Or may be something like this (not best in terms of Oracle)-

jdbcTemplate.update("MERGE INTO app_role_data x USING dual d ON (x.app_name = ?) WHEN MATCHED THEN UPDATE SET x.xml_blob = ? WHEN NOT MATCHED THEN INSERT(app_name, xml_blob) VALUES(?, ?)",......

edited Aug 13 '12 at 20:35

answered Aug 13 '12 at 20:17

Anjan Biswas

7,746
5
47
77

The failure is identical even if the query is simply "INSERT INTO app_role_data(app_name, xml_blob) VALUES(?,?)" – nsayer Aug 13 '12 at 21:08
can you run `desc app_role_data` and post the output? I am thinking that something is going on with the `CLOB` column. – Anjan Biswas Aug 13 '12 at 21:36
looks like your column `xml_blob` is of type `BLOB` and you are creating a `CLOB` using `lobCreator.setClobAsString`. Can you try creating a blob instance using `setBlobAsBytes`? and then use that instead.. – Anjan Biswas Aug 13 '12 at 21:46
The schema creation script has "create table app_role_data(app_name varchar2(64), xml_blob clob);" to create that table. – nsayer Aug 13 '12 at 23:00

Spring JDBC lob insertion fails: Invalid column type

2 Answers2