2 Replies Latest reply: Aug 14, 2012 2:42 PM by 955757 RSS

    Spring JDBC - inserting clob column fails with "invalid column type"

    955757

      I'm at my wit's end.

      Spring 3.0.5.RELEASE. Oracle JDBC driver 10.2.0.3.0. Server is 11g - 11.2.0.2.0 (64 bit Linux).

      The table:


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


      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 passed in from the context.

      Don't get too hung up on the query - it fails the exact same way if the query is "INSERT INTO app_role_data(app_name, xml_blob) VALUES(?,?)".

      The exception that results:

      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)

      Now, I don't necessarily expect Spring experts here - I've also posted this question over at the SpringSource forums.

      But this is AWFULLY elementary. I just can't begin to believe that inserting a CLOB is broken this badly.