使用spring StoredProcedure插入oracle clob IN参数时克服了32k的限制

本文介绍了使用spring StoredProcedure插入oracle clob IN参数时克服了32k的限制的处理方法，对大家解决问题具有一定的参考价值，需要的朋友们下面随着小编来一起学习吧！ 问题描述

环境:oracle 11g，spring-jdbc-3.2.2-RELEASE.jar，JDK 1.7，Oracle UCP驱动程序.

Environment: oracle 11g, spring-jdbc-3.2.2-RELEASE.jar, JDK 1.7, Oracle UCP driver.

我有一个存储过程，该存储过程将记录插入到带有CLOB列的表中. SP在其他IN和OUT参数中具有CLOB输入参数.我的Java代码使用Spring StoredProcedure调用存储过程:

I have a stored procedure which insert record to a table with CLOB column. The SP has a CLOB input argument among other IN and OUT arguments. My Java code uses Spring StoredProcedure to call the stored procedure:

public class MyClass extends StoredProcedure { public MyClass(){ ..... declareParameter(new SqlParameter("content", Types.CLOB)); ..... } public void insert(){ HashMap<String,Object> params = new HashMap<String, Object>(37); String bigContent = ....; // which contains ASCII chars in my test .... params.put("content", new SqlLobValue(bigContent)); .... execute(params); } }

如果bigContent具有< 32k个字符.如果bigContent有50K个字符，则它不起作用. 我还测试了使用jdbcTemplate和SqlLobValue直接插入表中的情况，与bigContent配合使用时，一切正常，有50K个字符.

The code works fine if bigContent has < 32k chars. If bigContent has, say 50K, chars, it didn't work. I also tested using jdbcTemplate and SqlLobValue to insert into the table directly, everything works fine with bigContent has 50K chars.

我想使用SP，因为它可以处理很多其他事情，并且比分别调用多个SQL插入，更新和查询语句更有效.

I want to use the SP as it does a whole bunch of other stuff and is more efficient than invoking multiple SQL insert, update, and query statements separately.

任何人都知道如何使其与SP一起使用吗?还是如果bigContent具有> 32K字符，这是我必须以不同方式处理的限制?

Anyone know how to get it to work with SP? Or this is the limit I have to handle it differently if bigContent has > 32K chars?

推荐答案

如果您将数据作为字符串发送，则PL/SQL的硬限制为32k个字符.如果参数是CLOB，则可以首先创建一个临时LOB，用数据填充它，然后使用CLOB对象调用PL/SQL过程.

PL/SQL has a hard limit of 32k chars if you send the data as a character string. If the parameter is a CLOB you can first create a temp LOB, fill it up with data and then call your PL/SQL procedure with the CLOB object.