Oracle - Updating CLOB Column

Question

I am receiving a very strange error, not sure where in my code this is coming from and how to solve it: ORA-22920: row containing the LOB value is not locked

Here is my code. I've never seen that message before.

CREATE OR REPLACE PROCEDURE update_clob (
  p_company_id       NUMBER,
  p_table_name       VARCHAR2,
  p_column_name      VARCHAR2,
  p_clob_content     CLOB) IS

v_clob_ref           CLOB;
v_amount             NUMBER;
v_offset             NUMBER;
v_length             NUMBER;
v_content            VARCHAR2(4000);
v_update             VARCHAR2(32000);
v_insert             VARCHAR2(32000);
v_select             VARCHAR2(32000);

BEGIN
  v_offset    := 1;
  v_amount    := 4000;
  v_length    := DBMS_LOB.GETLENGTH(p_clob_content);


  /*** Reset CLOB Column Value ***/
  v_update    := '
    UPDATE ' || p_table_name || '
    SET ' || p_column_name || ' = EMPTY_CLOB()
    WHERE company_id = ' || p_company_id ;

   EXECUTE IMMEDIATE v_update;

  /*** Loop through 4k chunks of CLOB ***/
  WHILE v_offset <= v_length LOOP
    v_select := '
      SELECT ' || p_column_name || '
      FROM ' || p_table_name || '
      WHERE company_id = ' || p_company_id;

    EXECUTE IMMEDIATE v_select INTO v_clob_ref;

    v_content := DBMS_LOB.SUBSTR(p_clob_content, v_amount, v_offset);
    DBMS_LOB.WRITEAPPEND(v_clob_ref, LENGTH(v_content), v_content);
    COMMIT;

    v_offset := v_offset + v_amount;
  END LOOP;

END update_clob;

Many thanks!

Answer 1

Move the COMMIT to after the END LOOP:

    DBMS_LOB.WRITEAPPEND(v_clob_ref, LENGTH(v_content), v_content);

    v_offset := v_offset + v_amount;
  END LOOP;

  COMMIT;
END update_clob;

dbms_lob routines are not SQL calls that atomically change rows, so Oracle requires the row itself be locked while you are writing to it, and that commit after the writeappend is ending your transaction and unlocking the row before you're done with it.