How To Update Empty String To Oracle Clob

I know it works by using SQL update activity set REFERENCE = EMPTY_CLOB() where id = ? But I cannot do like this, I cannot hard coded 'EMPTY_CLOB()' in SQL. I used the way like t

Solution 1:

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

Use setCharacterStream() instead:

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

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

Another option would be to simply set that column to NULL

Edit:

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

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).