从java字符串插入到CLOB类型时的ORA-01461

Question

我在ibatis sql配置文件中将java字符串类型映射到CLOB类型，并在使用此配置插入记录时遇到ORA错误。当字符串的长度是4000个字符或更少时，这似乎是工作文件。为了测试长字符串，我创建了一个字段(DATA_CODE和SCHEMA_CODE)中包含5000个字符的随机字符串。

我的sql文件如下所示：

<parameterMap class="map" id="map">
<parameter property="QUERY_HASH" javaType="java.math.BigDecimal" jdbcType="NUMBER" />
<parameter property="LOCATION" javaType="java.lang.String" jdbcType="VARCHAR2" />
<parameter property="HOST_NAME" javaType="java.lang.String" jdbcType="VARCHAR2" />
<parameter property="SERVER_PORT" javaType="java.lang.String" jdbcType="VARCHAR2" />
<parameter property="DATA_CODE" javaType="java.lang.String" jdbcType="CLOB" />
<parameter property="SCHEMA_CODE" javaType="java.lang.String" jdbcType="CLOB" />
<parameter property="CREATED" javaType="java.util.Date" jdbcType="DATE" />
</parameterMap> 
<insert id="INSERT__QUERY_CACHE" parameterClass="map">
    <![CDATA[
    INSERT INTO "QUERY_CACHE" (query_hash, location, host_name, server_port, data_code, schema_code, created)
    SELECT #QUERY_HASH#, #LOCATION#, #HOST_NAME#, #SERVER_PORT#, #DATA_CODE#, #SCHEMA_CODE#, #CREATED# FROM DUAL WHERE NOT EXISTS (SELECT * FROM 
    SAVRO_CONN_QUERY_CACHE WHERE query_hash = #QUERY_HASH# and location=#LOCATION# and host_name = #HOST_NAME# and server_port = #SERVER_PORT#)
    ]]>
</insert>

我的java代码是：

Map<String, Object> iterMap = new HashMap<String, Object>();
iterMap.put("QUERY_HASH", key);
iterMap.put("LOCATION", cacheEntry.className);
iterMap.put("HOST_NAME", hostName);
iterMap.put("SERVER_PORT", serverPort);
////// junk string - START
StringBuilder builder = new StringBuilder();
builder.append("5000 chars string");
builder.append(RandomStringUtils.random(5000));
iterMap.put("DATA_CODE", builder.toString());
iterMap.put("SCHEMA_CODE", builder.toString());
//// junk string - END
iterMap.put("CREATED", new Date(System.currentTimeMillis()));
insert(QRY__INSERT, iterMap);

异常跟踪为：

        org.springframework.jdbc.UncategorizedSQLException: SqlMapClient operation; uncategorized SQLException for SQL []; SQL state [72000]; error code [1461];
    --- The error occurred in com/mycomp/sql_config.xml.
    --- The error occurred while applying a parameter map.
    --- Check the INSERT__QUERY_CACHE-InlineParameterMap.
    --- Check the statement (update failed).
    --- Cause: java.sql.SQLException: ORA-01461: can bind a LONG value only for insert into a LONG column
    ; nested exception is com.ibatis.common.jdbc.exception.NestedSQLException:
    --- The error occurred in com/mycomp/sql_config.xml.
    --- The error occurred while applying a parameter map.
    --- Check the INSERT__QUERY_CACHE-InlineParameterMap.
    --- Check the statement (update failed).
    --- Cause: java.sql.SQLException: ORA-01461: can bind a LONG value only for insert into a LONG column

    at org.springframework.jdbc.support.AbstractFallbackSQLExceptionTranslator.translate(AbstractFallbackSQLExceptionTranslator.java:83)
    at org.springframework.jdbc.support.AbstractFallbackSQLExceptionTranslator.translate(AbstractFallbackSQLExceptionTranslator.java:80)
    at org.springframework.orm.ibatis.SqlMapClientTemplate.execute(SqlMapClientTemplate.java:206)
    at org.springframework.orm.ibatis.SqlMapClientTemplate.insert(SqlMapClientTemplate.java:367)
    at com.mycomp.mypackage.MyCache.saveCache(MyCache.java:142)
    at sun.reflect.NativeMethodAccessorImpl.invoke0(Native Method)
    at sun.reflect.NativeMethodAccessorImpl.invoke(NativeMethodAccessorImpl.java:62)
    at sun.reflect.DelegatingMethodAccessorImpl.invoke(DelegatingMethodAccessorImpl.java:43)
    at java.lang.reflect.Method.invoke(Method.java:483)
    at org.springframework.scheduling.support.ScheduledMethodRunnable.run(ScheduledMethodRunnable.java:64)
    at org.springframework.scheduling.support.DelegatingErrorHandlingRunnable.run(DelegatingErrorHandlingRunnable.java:53)
    at java.util.concurrent.Executors$RunnableAdapter.call(Executors.java:511)
    at java.util.concurrent.FutureTask.runAndReset(FutureTask.java:308)
    at java.util.concurrent.ScheduledThreadPoolExecutor$ScheduledFutureTask.access$301(ScheduledThreadPoolExecutor.java:180)
    at java.util.concurrent.ScheduledThreadPoolExecutor$ScheduledFutureTask.run(ScheduledThreadPoolExecutor.java:294)
    at java.util.concurrent.ThreadPoolExecutor.runWorker(ThreadPoolExecutor.java:1142)
    at java.util.concurrent.ThreadPoolExecutor$Worker.run(ThreadPoolExecutor.java:617)
    at java.lang.Thread.run(Thread.java:745)
    Caused by: com.ibatis.common.jdbc.exception.NestedSQLException:
    --- The error occurred in com/mycomp/sql_config.xml.
    --- The error occurred while applying a parameter map.
    --- Check the INSERT__QUERY_CACHE-InlineParameterMap.
    --- Check the statement (update failed).
    --- Cause: java.sql.SQLException: ORA-01461: can bind a LONG value only for insert into a LONG column

    at com.ibatis.sqlmap.engine.mapping.statement.MappedStatement.executeUpdate(MappedStatement.java:107)
    at com.ibatis.sqlmap.engine.impl.SqlMapExecutorDelegate.insert(SqlMapExecutorDelegate.java:393)
    at com.ibatis.sqlmap.engine.impl.SqlMapSessionImpl.insert(SqlMapSessionImpl.java:82)
    at org.springframework.orm.ibatis.SqlMapClientTemplate$8.doInSqlMapClient(SqlMapClientTemplate.java:369)
    at org.springframework.orm.ibatis.SqlMapClientTemplate.execute(SqlMapClientTemplate.java:203)
    ... 15 more
    Caused by: java.sql.SQLException: ORA-01461: can bind a LONG value only for insert into a LONG column

    at oracle.jdbc.driver.T4CTTIoer.processError(T4CTTIoer.java:445)
    at oracle.jdbc.driver.T4CTTIoer.processError(T4CTTIoer.java:396)
    at oracle.jdbc.driver.T4C8Oall.processError(T4C8Oall.java:879)
    at oracle.jdbc.driver.T4CTTIfun.receive(T4CTTIfun.java:450)
    at oracle.jdbc.driver.T4CTTIfun.doRPC(T4CTTIfun.java:192)
    at oracle.jdbc.driver.T4C8Oall.doOALL(T4C8Oall.java:531)
    at oracle.jdbc.driver.T4CPreparedStatement.doOall8(T4CPreparedStatement.java:207)
    at oracle.jdbc.driver.T4CPreparedStatement.executeForRows(T4CPreparedStatement.java:1044)
    at oracle.jdbc.driver.OracleStatement.doExecuteWithTimeout(OracleStatement.java:1329)
    at oracle.jdbc.driver.OraclePreparedStatement.executeInternal(OraclePreparedStatement.java:3584)
    at oracle.jdbc.driver.OraclePreparedStatement.execute(OraclePreparedStatement.java:3685)
    at oracle.jdbc.driver.OraclePreparedStatementWrapper.execute(OraclePreparedStatementWrapper.java:1376)
    at org.apache.commons.dbcp.DelegatingPreparedStatement.execute(DelegatingPreparedStatement.java:172)
    at org.apache.commons.dbcp.DelegatingPreparedStatement.execute(DelegatingPreparedStatement.java:172)
    at com.ibatis.sqlmap.engine.execution.SqlExecutor.executeUpdate(SqlExecutor.java:80)
    at com.ibatis.sqlmap.engine.mapping.statement.MappedStatement.sqlExecuteUpdate(MappedStatement.java:216)
    at com.ibatis.sqlmap.engine.mapping.statement.MappedStatement.executeUpdate(MappedStatement.java:94)
    ... 19 more

我们使用ibatis 2.3.4和Spring3.3，我在其他文章中搜索过类似的问题，但我找不到任何线索。有任何线索请..

Answer 1

显然，没有一种简单的方法可以从Java在Oracle中插入大值，因为JDBC驱动程序本身并不支持Reader和Inputstream。这在SO上的另一个主题中进行了解释：Java: How to insert CLOB into oracle database

Answer 2

我在做insert into .. select if not exists。我的要求是只有在表中不存在数据时才插入数据。之后它就起作用了

• 将其拆分为两个单独的查询-一个用于select，一个用于insert。
• 由于我无论如何都要发送一个映射参数，因此删除了paramMap xml。
• 追加了CLOB以查询xml中的参数

在Java中，

Map<String, Object> selectMap = new HashMap<String, Object>();
selectMap.put("QUERY_HASH", key);
selectMap.put("LOCATION", cacheEntry.className);
selectMap.put("HOST_NAME", hostName);
selectMap.put("SERVER_PORT", serverPort);

Integer count = (Integer) queryForObject(QRY__SELECT_FROM_CACHE, selectMap);
if(count == 0) {
    Map<String, Object> iterMap = new HashMap<String, Object>();
    iterMap.put("QUERY_HASH", key);
    iterMap.put("LOCATION", cacheEntry.className);
    iterMap.put("HOST_NAME", hostName);
    iterMap.put("SERVER_PORT", serverPort);
    iterMap.put("DATA_CODE", cacheEntry.dataCode);
    iterMap.put("SCHEMA_CODE", cacheEntry.schemaCode);
    insert(QRY__INSERT, iterMap);
}