环境: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.
更多推荐
使用spring StoredProcedure插入oracle clob IN参数时克服了32k的限制
发布评论