oracle 使用 JDBC 从长字符串创建 CLOB
声明：本页面是StackOverFlow热门问题的中英对照翻译，遵循CC BY-SA 4.0协议，如果您需要使用它，必须同样遵循CC BY-SA许可，注明原文地址和作者信息，同时你必须将它归于原作者（不是我）：StackOverFlow
原文地址: http://stackoverflow.com/questions/5067485/
Warning: these are provided under cc-by-sa 4.0 license. You are free to use/share it, But you must attribute it to the original authors (not me):
StackOverFlow
Create CLOB from long string using JDBC
提问by taurus
I have the following query:
我有以下查询：
select id from table1 where some_func(?) = 1;
where some_funcis a function which allows its arguments to be either VARCHAR2 or CLOB, and ?is some string, which could be really long.
wheresome_func是一个函数，它允许它的参数是 VARCHAR2 或 CLOB，并且?是一些字符串，它可能很长。
I am trying to use the following code to bind variables:
我正在尝试使用以下代码来绑定变量：
stmt.setObject(i+1, obj);
but in case of string.length()> 4000 I get the following error:
但在string.length()> 4000 的情况下，我收到以下错误：
java.sql.SQLException: ORA-01460: unimplemented or unreasonable conversion requested
for an obvious reason: the VARCHAR2 size limit is 4000 characters.
原因很明显：VARCHAR2 大小限制为 4000 个字符。
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);
}
which gave a different error:
这给出了一个不同的错误：
ORA-22922: nonexistent LOB value
The last idea I tried was to create a CLOB using oracle.sql.CLOB.createTemporary()method but it failed because of the following exception:
我尝试的最后一个想法是使用oracle.sql.CLOB.createTemporary()方法创建一个 CLOB，但由于以下异常而失败：
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?
我究竟做错了什么？有没有其他可能性来做到这一点？
回答by taurus
The CLOB could be created in a simple manner:
CLOB 可以通过一种简单的方式创建：
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.
那么这些块当然应该被释放。
回答by a_horse_with_no_name
From my experience setCharacterStream() is much more reliable than setClob()
根据我的经验 setCharacterStream() 比 setClob() 可靠得多
String s = (String) obj; StringReader stringReader = new StringReader(s); stmt.setCharacterStream(i + 1, stringReader , s.length());
and it works without the need to create CLOB objects
它无需创建 CLOB 对象即可工作
