如何将空字符串更新为oracle Clob [英] How to update empty string to oracle Clob

问题描述

我知道它的工作原理是使用SQL

I know it works by using SQL

update activity set REFERENCE = EMPTY_CLOB() where id = ?

但我不能这样做，我不能在SQL中硬编码'EMPTY_CLOB（）'。
我使用如下方式：

But I cannot do like this, I cannot hard coded 'EMPTY_CLOB()' in SQL. I used the way like the following:

String empty_string = "";
conn = getConnection();

pStmt = conn.prepareStatement("SELECT REFERENCE FROM activity WHERE ID = ? FOR UPDATE");
pStmt.setLong(1, 1);
rset = pStmt.executeQuery();
Clob clob = null;
while (rset.next()) {
    clob = rset.getClob(1);
    Writer writer = adapter.getCharacterOutputStream(clob);
    writer.write(empty_string);         
    writer.flush();
    writer.close();
}

pStmt = conn.prepareStatement("update activity set REFERENCE = ? WHERE ID = ?");
pStmt.setClob(1, clob);
pStmt.setLong(2, 1);
pStmt.executeUpdate();

但它不起作用。 clob没有更新为空字符串，它仍然存储为以前的值。

But It didn't work. the clob didn't be updated to empty string, it still stored as previous value.

任何团体都可以帮我这个吗？

Any body can help me on this?

推荐答案

正如我在你的另一个问题：根据我的经验，getClob（）和setClob（）无法正常工作。

As I have already mentionued in your other question: in my experience getClob() and setClob() don't work properly.

使用 setCharacterStream（）代替：

StringReader clob = new StringReader("");
pStmt = conn.prepareStatement("update activity set REFERENCE = ? WHERE ID = ?");
pStmt.setCharacterStream(1, clob, 0);
pStmt.setLong(2, 1);
pStmt.executeUpdate();

这样你也可以在更新之前删除不必要的SELECT，这也将提高性能。

That way you can also remove the unnecessary SELECT before updating, which will improve performance as well.

另一种选择是将该列设置为 NULL

Another option would be to simply set that column to NULL

修改：

对于较新的驱动程序（11.x），您可能还想尝试使用 CLOB列上的setString（）和 getString（）。

With newer drivers (11.x) you might also want to try to use setString() and getString() on the CLOB column.

只有在使用您打算在跨越多个语句的事务期间保留的LOB定位器时才需要锁定行（至少这是我的理解）链接参考手册）。

The locking of the row should only be necessary when you use a LOB locator that you intend to keep during a transaction that spans more than one statement (at least that's my understanding of the linked reference to the manual).

这篇关于如何将空字符串更新为oracle Clob的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持IT屋！