Oracle: how to resolve an ORA-22920 error? (row containing the LOB value is not locked)
Sep
27
Written by:
9/27/2013 9:22 AM
When changing some stored procedure which generated doublons due to the fact that it made always insertions instead of insertions or updates, I got the fatal Oracle error - ORA-22920 row containing the LOB value is not locked - when trying to update the BLOB field.
In fact, Oracle accepts the term FOR UPDATE which indicates that the record will be locked.
For instance, the following query works fine and resolve the problem:
1.IF v_nbrecord=0 THEN
2.
3. INSERT INTO T_INSTRUMENT_XML_DEFS (sicovam, version, xml, modified)
4. VALUES (newsicovam, v_instrument_version, empty_clob(), SYSDATE)
5. RETURNING xml INTO newxml;
6. ELSE
7. SELECT xml INTO newxml FROM T_INSTRUMENT_XML_DEFS WHERE sicovam=newsicovam AND version=v_instrument_version FOR UPDATE;
8. END IF;