Oracle - Typing to insert / update CLOB column

Question

I'm trying to re-initialize / update a CLOB column and running into some issues which I believe are related to LOB LOCATOR, but need some help.

After the first UPDATE statement, I get this: ORA-21560: argument 2 is null, invalid, or out of range

Here is my code:

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

v_clob_ref           CLOB;
v_amount             NUMBER;
v_offset             NUMBER;
v_length             NUMBER;
v_update             VARCHAR2(1000);
v_select             VARCHAR2(1000);

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

  v_update    := '
    UPDATE ' || p_table_name || '
    SET ' || p_column_name || ' = EMPTY_CLOB()
    WHERE company_id = ' || p_company_id || '
    RETURNING ' || p_column_name || ' INTO :x';

  EXECUTE IMMEDIATE v_update USING OUT v_clob_ref;
  DBMS_LOB.WRITEAPPEND(v_clob_ref, v_length, DBMS_LOB.SUBSTR(p_clob_content, v_amount, v_offset));

  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;
    DBMS_LOB.WRITEAPPEND(v_clob_ref, v_length, DBMS_LOB.SUBSTR(p_clob_content, v_amount, v_offset));

    v_offset := v_offset + v_amount;
  END LOOP;
END update_clob;

Answer 1

Here you are assigning the length of the entire LOB to v_length:

 v_length    := DBMS_LOB.GETLENGTH(p_clob_content);

But here you are asking Oracle to write that many bytes, but only providing a 4000 byte buffer:

 DBMS_LOB.WRITEAPPEND(v_clob_ref, v_length, DBMS_LOB.SUBSTR(p_clob_content, v_amount, v_offset));

Try specifying v_amount instead:

 DBMS_LOB.WRITEAPPEND(v_clob_ref, v_amount, DBMS_LOB.SUBSTR(p_clob_content, v_amount, v_offset);

But as that will get you into trouble on the very last chunk which will be less than 4K, you should probably assign the buffer to a variable and do a length on it:

DECLARE
  var_buffer varchar2(4000);
BEGIN
  var_buffer := DBMS_LOB.SUBSTR(p_clob_content, v_amount, v_offset);
  DBMS_LOB.WRITEAPPEND(v_clob_ref, LENGTH(var_buffer), var_buffer);
END;