如何将空字符串更新为 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屋！