Problems with JDBCTemplate and CLOB insertion

[nsayer@kfu.com]

This is with Spring 3.0.5.RELEASE.

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);
                            }
                    });

But note that this fails the same way with a simple INSERT on the clob column.

lobHandler is an instance of OracleLobHandler, injected via the context.

The table:

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

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)

WTF?

[nsayer@kfu.com]

Seriously? Nobody has any idea at all what I could try to either fix or diagnose this further?

Brick wall?

Inserting a CLOB value?

Really?

[nsayer@kfu.com]

This fails the same way with Spring 3.1.1.RELEASE.

And with the Oracle 11.2.0.2.0 JDBC driver.

So I am the only programmer on the entire planet that can't insert a CLOB into Oracle with Spring? Inconceivable.

[nsayer@kfu.com]

Turns out, execute() takes a PreparedStatementCallback, and update() takes a PreparedStatementSetter.... but both of them take Object, so the compiler can't help.

Thanks a ton, Spring!

[prashanthbs]

This fails the same way with Spring 3.1.1.RELEASE.

And with the Oracle 11.2.0.2.0 JDBC driver.

So I am the only programmer on the entire planet that can't insert a CLOB into Oracle with Spring? Inconceivable.

No Frustration, Take it easy.

Well i have a simple test case as below to suit your inputs to show it works fine with the above .

CREATE TABLE app_role_data(
  APP_NAME        VARCHAR2(64)                 NOT NULL,
  XML_CLOB       CLOB                          NOT NULL
)

Get the spring configuration below

<?xml version="1.0" encoding="UTF-8"?>
<beans xmlns="http://www.springframework.org/schema/beans"
       xmlns:xsi="http://www.w3.org/2001/XMLSchema-instance"
       xmlns:context="http://www.springframework.org/schema/context"
       xsi:schemaLocation="http://www.springframework.org/schema/beans
                           http://www.springframework.org/schema/beans/spring-beans-3.1.xsd
                           http://www.springframework.org/schema/context
                           http://www.springframework.org/schema/context/spring-context-3.1.xsd">
                           


<bean id="dataSource" class="org.springframework.jdbc.datasource.DriverManagerDataSource" >
        <property name="driverClassName" value="oracle.jdbc.driver.OracleDriver"/>
        <property name="url" value="Fill all the DB credentials  "/>
        <property name="username" value=""/>
        <property name="password" value=""/>
    </bean>
  
<bean id="lobHandler" class="org.springframework.jdbc.support.lob.OracleLobHandler">
        <property name="nativeJdbcExtractor" ref="nativeJdbcExtractor"/>
    </bean>

<bean id="nativeJdbcExtractor" class="org.springframework.jdbc.support.nativejdbc.CommonsDbcpNativeJdbcExtractor"/>  
</beans>

Update the URL, User name, Password of your DB in the above configuration xml file.

import java.sql.PreparedStatement;
import java.sql.SQLException;

import javax.sql.DataSource;

import org.springframework.context.ApplicationContext;
import org.springframework.context.support.ClassPathXmlApplicationContext;
import org.springframework.jdbc.core.JdbcTemplate;
import org.springframework.jdbc.core.PreparedStatementSetter;
import org.springframework.jdbc.support.lob.LobHandler;


public class Main {

	public static void main(String[] args) {
		ApplicationContext ac = new ClassPathXmlApplicationContext("jdbc-config.xml", Main.class);
	    DataSource dataSource = (DataSource) ac.getBean("dataSource");
	    final LobHandler lobHandler = (LobHandler) ac.getBean("lobHandler");
	    JdbcTemplate jdbcTemplate = new JdbcTemplate(dataSource);
	    jdbcTemplate.update("MERGE INTO   app_role_data x  USING   (    " +
	    		" SELECT  :1 APP_NAME, :2 XML_CLOB FROM DUAL) d  ON   " +
	    		" (x.APP_NAME = d.APP_NAME) " +
	    		" WHEN MATCHED THEN " +
	    		"    UPDATE SET x.XML_CLOB = d.XML_CLOB " +
	    		" WHEN NOT MATCHED THEN INSERT  (APP_NAME, XML_CLOB) " +
	    		"   VALUES   (:3, :4)",  new PreparedStatementSetter(){	    	    
	      public void setValues(PreparedStatement ps) throws SQLException {
	        ps.setString(1, "99");
	        lobHandler.getLobCreator().setClobAsString(ps, 2, "Updated Clob data");
	        ps.setString(3, "99");
	        lobHandler.getLobCreator().setClobAsString(ps, 4, "Inserted Clob data");

	      }
	    });

	}

Just run the main class which would do the insert and updates as per your merge statement.

Prashanth