使用JDBC从长字符串创建CLOB [英] Create CLOB from long string using JDBC

问题描述

我有以下查询:

select id from table1 where some_func(?) = 1;

其中some_func是一个允许其参数为VARCHAR2或CLOB的函数，而?是一些字符串，可能真的很长.

where some_func is a function which allows its arguments to be either VARCHAR2 or CLOB, and ? is some string, which could be really long.

我正在尝试使用以下代码来绑定变量:

I am trying to use the following code to bind variables:

stmt.setObject(i+1, obj);

但在string.length()> 4000的情况下，出现以下错误:

but in case of string.length() > 4000 I get the following error:

java.sql.SQLException: ORA-01460: unimplemented or unreasonable conversion requested

的原因很明显:VARCHAR2的大小限制为4000个字符.

for an obvious reason: the VARCHAR2 size limit is 4000 characters.

然后我尝试使用以下代码:

I then tried to use the following code:

if(obj instanceof String && ((String) obj).length() >= 4000) {
  String s = (String) obj;
  StringReader stringReader = new StringReader(s);
  stmt.setClob(i+1, stringReader, s.length());
} else {
  stmt.setObject(i+1, obj);
}

给出了另一个错误:

ORA-22922: nonexistent LOB value

我尝试的最后一个想法是使用oracle.sql.CLOB.createTemporary()方法创建CLOB，但由于以下异常而失败:

The last idea I tried was to create a CLOB using oracle.sql.CLOB.createTemporary() method but it failed because of the following exception:

java.lang.ClassCastException:
  org.apache.commons.dbcp.PoolingDataSource$PoolGuardConnectionWrapper 
  cannot be cast to oracle.jdbc.OracleConnection

我做错了什么?还有其他可能性吗?

What am I doing wrong? Are there any other possibilities to do this?

推荐答案

可以通过简单的方式创建CLOB:

The CLOB could be created in a simple manner:

if(obj instanceof String && ((String) obj).length() >= 4000) {
    Clob clob = connection.createClob();
    clob.setString(1, (String) obj);
    stmt.setClob(i+1, clob);
}

那么这些衣橱当然应该被释放了.

Then these clobs should be freed of course.

这篇关于使用JDBC从长字符串创建CLOB的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持IT屋！