Oracle appending to a clob-CodePudding

I have a working scenario that adds a newline to a CLOB every time during an update.

I have a similar scenario, below, that doesn't seem to be working. I can't understand why and was hoping someone can explain what the problem is as I would prefer to have the newline code embedded in the procedure instead of having the user having to add it for every update.


-- Works 

ALTER SESSION SET NLS_DATE_FORMAT = 'MMDDYYYY HH24:MI:SS';

CREATE table t(
seq_num integer  GENERATED BY DEFAULT AS IDENTITY (START WITH 1) NOT NULL,
c CLOB,
create_date DATE DEFAULT SYSDATE
);
/

insert into t (c) values (
      rpad('X',20,'X')
  );
/

create or replace procedure lob_append( p_id in number, p_text in varchar2 )
 as
 l_clob clob;
 begin

 select c into l_clob from t where seq_num = p_id for update;

 dbms_lob.writeappend( l_clob, length(p_text), p_text );
 end;
/

 select * from t;
 /

exec lob_append(1, chr(10)|| rpad('Z',20,'Z'));
/

select * from t;
 /

-- Doesn't work 

DROP table t;
/

CREATE table t(
seq_num integer  GENERATED BY DEFAULT AS IDENTITY (START WITH 1) NOT NULL,
c CLOB,
create_date DATE DEFAULT SYSDATE
);
/

insert into t (c) values (
      rpad('X',20,'X')
  );
/

create or replace procedure lob_append( p_id in number, p_text in varchar2 )
 as
 l_clob clob;
 begin

 select c into l_clob from t where seq_num = p_id for update;

-- newline not added
l_clob := l_clob || chr(10);

 dbms_lob.writeappend( l_clob, length(p_text), p_text );
 end;
/

 select * from t;
 /

-- Data not added
 exec lob_append(1, rpad('Z',20,'Z'));

select * from t;
 /

CodePudding user response：

select c into l_clob from t where seq_num = p_id for update;

At this point in your code, l_clob contains a lob locator (let's call it LOCATOR_A) which points to a specific string in the database. If you call dbms_lob.writeappend(l_clob, ...) it will update the string at LOCATOR_A with the new value.

l_clob := l_clob || chr(10);

When you call this line, the first part l_clob || chr(10) creates a NEW lob locator (let's call it LOCATOR_B) pointing to a temporary CLOB location, and gives it the string value newline. The second part l_clob := value assigns the temporary lob locator LOCATOR_B to the l_clob variable. At this point, you've lost the reference to the permanent lob pointer LOCATOR_A that's stored in the database.

dbms_lob.writeappend( l_clob, length(p_text), p_text )

Now you update the temporary lob LOCATOR_B. It is immediately discarded when the procedure returns. The database clob which LOCATOR_A points to is not changed.

I would recommend adding the newline to your varchar2 instead, so you don't create a new temporary clob.

create or replace procedure lob_append( p_id in number, p_text in varchar2 )
 as
 l_clob clob;
 begin

 select c into l_clob from t where seq_num = p_id for update;

 p_text := chr(10) || p_text;

 dbms_lob.writeappend( l_clob, length(p_text), p_text );
 end;
/