Oracle lob field operations

Today, we have a function that requires operations on the clob field. If you encounter several problems during this period, you can solve them. Remember to avoid repeating the same mistakes in the future.

Error 1:

ORA-22920: the row containing the lob value is not locked

The cause of this problem is that for update is not added when the select lob field is output. The Oracle official documents describe this error:

Reference text code
1. ORA-22920 row containing the lob value is not locked
2.
3. cause: the row containing the lob value must be locked before updating the lob value.

4.
5. Action: Lock the row containing the lob value before updating the lob value.
ORA-22920 row containing the lob value is not locked

Cause: the row containing the lob value must be locked before updating the lob value.

Action: Lock the row containing the lob value before updating the lob value.

So I added the for update to the test and found that the error prompt changed to the following:

 

 

Error 2:
ORA-01002: Read violation order

This problem occurs because the autocommit attribute value of connection is true.

SQL code
1. Conn. setautocommit (false );
2.
3 .......
4.
5. lob operations
6.
7 ......
8.
9. Conn. Commit ();
10.
11. Conn. setautocommit (true );
Conn. setautocommit (false );

......

Lob operation

.....

Conn. Commit ();

Conn. setautocommit (true );
 

You can.

 

In addition, do not select non-Lob fields when selecting the first step. If a table has multiple lob fields, it is best to operate the table in the order of fields in the table, otherwise, an error may occur.