Database Software

Getting "ORA-22920: row containing the LOB value is not locked" error when updating the

PROCEDURE UPDT_QI_FOR_RATE_EXPIRY(P_Object_id in number,
P_RATESTATUS in varchar2 DEFAULT 'Incomplete') IS
v_no_of_rows_updated number default 0;
V_TIMESTAMP VARCHAR(30) DEFAULT NULL;
V_LOCKSTAMP VARCHAR(30) DEFAULT NULL;
V_CLOB CLOB;
V_COUNT number default 0;

p xmlparser.parser;
l_xmldoc dbms_xmldom.DOMDocument;
l_root dbms_xmldom.DOMNode;
l_node1 dbms_xmldom.DOMNode;
l_node2 dbms_xmldom.DOMNode;
l_n dbms_xmldom.DOMNode;
l_nl1 dbms_xmldom.DOMNodeList;
l_nl2 dbms_xmldom.DOMNodeList;
begin

SELECT TO_CHAR(SYS_EXTRACT_UTC(SYSTIMESTAMP),'YYYY-MM-DD')||'T'||TO_CHAR(SYS_EXTRACT_UTC(SYSTIMESTAMP),'HH24:MI:SS') INTO V_TIMESTAMP FROM DUAL;
SELECT TO_CHAR(SYS_EXTRACT_UTC(SYSTIMESTAMP),'YYYYMMDDHH24MISS') || '.000' into V_LOCKSTAMP FROM DUAL;


UPDATE tnmab_ocean_rate_xml b
SET ocean_rate_xml = UPDATEXML(ocean_rate_xml, '/OceanOfferRate/RateStatus/text()', P_RATESTATUS,'/OceanOfferRate/LastUpdatedBy/text()', 'TNM_APPLN',
'/OceanOfferRate/LastUpdated/text()', V_TIMESTAMP,
'/OceanOfferRate/LockStamp/text()', V_LOCKSTAMP,'xmlns="http://com.oocl.schema.tnm.agreementbuilder"')
where b.rowid = (select b.rowid from TNMAB_OCEAN_RATE_XML b,xmltable(xmlnamespaces(default 'http://com.oocl.schema.tnm.agreementbuilder'), '/OceanOfferRate' PASSING
b.OCEAN_RATE_XML COLUMNS Object_ID NUMBER(20) PATH 'ObjectID') xtab
WHERE XTAB.OBJECT_ID = P_Object_id );

SELECT COUNT(*) INTO V_COUNT FROM TNMAB_APPROVAL_INFO WHERE OBJECT_ID = P_OBJECT_ID;
IF(V_COUNT >0) THEN
SELECT app_info INTO v_clob FROM TNMAB_APPROVAL_INFO WHERE OBJECT_ID = P_OBJECT_ID;-- FOR UPDATE NOWAIT;
p := xmlparser.newParser;
xmlparser.parseClob(p,v_clob);
L_XMLDOC := XMLPARSER.GETDOCUMENT(P);
l_root := dbms_xmldom.makeNode(l_xmldoc);
l_nl1 := DBMS_XMLDOM.getElementsByTagName(dbms_xmldom.getDocumentElement(l_xmldoc),'ActivationStatus');
l_node1 := DBMS_XMLDOM.item(l_nl1,0);
l_node1 := DBMS_XMLDOM.GETFIRSTCHILD(l_node1); -- VALUE

l_nl2 := DBMS_XMLDOM.getElementsByTagName(dbms_xmldom.getDocumentElement(l_xmldoc),'QILockStamp');
l_node2 := DBMS_XMLDOM.item(l_nl2,0);
l_node2 := DBMS_XMLDOM.GETFIRSTCHILD(l_node2); -- VALUE

dbms_xmldom.setNodeValue(l_node1, P_RATESTATUS);
DBMS_XMLDOM.SETNODEVALUE(L_NODE2, V_LOCKSTAMP);
DBMS_OUTPUT.PUT_LINE(DBMS_XMLDOM.GETNODEVALUE(L_NODE1));
dbms_output.put_line(DBMS_XMLDOM.GETNODEVALUE(L_NODE2));

DBMS_XMLDOM.WRITETOCLOB(L_ROOT, V_CLOB);_

DBMS_OUTPUT.PUT_LINE('** Done writing to clob '|| SYSTIMESTAMP);

dbms_output.put_line(v_clob);

update TNMAB_APPROVAL_INFO app set app.app_info = v_clob
WHERE APP.OBJECT_ID = P_OBJECT_ID;

DBMS_OUTPUT.PUT_LINE('** Done updating to clob '|| SYSTIMESTAMP);
exit
xmlparser.freeParser(p);
dbms_xmldom.freeDocument(l_xmldoc);
DBMS_LOB.FREETEMPORARY(V_CLOB);
end if;
end UPDT_QI_FOR_RATE_EXPIRY;
BEGIN
DBMS_OUTPUT.PUT_LINE('** Search starting at '|| SYSTIMESTAMP);
UPDT_QI_FOR_RATE_EXPIRY(100000006142670, 'Incomplete');

END;