Help with PL/SQL - CLOB

[anyoneokay]

I have code another exmaple as below, for inserting or updating clob column.
I think it can help you to resolve your problem.

the data from t_src_clob
NO CONTENT(clob)
--------------------
1 xxx
2 yyy

and i want insert a row to t_dest_clob
NO CONTENT(clob)
--------------------
3
then set t_src_clob's content value(whose no is 'xxx' ) for t_dest_clob's content.

At last, the data from t_dest_clob will become
NO CONTENT(clob)
--------------------
3 xxx

The original data for testing：

--drop table t_src_clob;
--drop table t_dest_clob;
create table t_src_clob
(
  no      number,
  content clob
);
alter table t_src_clob
  add constraint T_SRC_CLOB_PK primary key(no);
  
create table t_dest_clob
(
  no      number,
  content clob
);
alter table t_dest_clob
  add constraint T_DEST_CLOB_PK primary key(no);

insert into t_src_clob  values(1,'xxx');
insert into t_src_clob  values(2,'yyy');

commit;

select * from t_src_clob;
select * from t_dest_clob;

Procedure and function for update clob:

create or replace function getclob(ip_table_name  in varchar2,  --the clob table
                                   ip_PK_column   in varchar2,  --the pk column name of clob table
                                   ip_PK_value    in number,    --the value of pk column
                                   ip_clob_column in varchar2   --the clob column'name
                                  ) return clob is 
  lv_clob   clob;
  lv_amount number := 2000;
  lv_offset number := 1;
  lv_sqlstr varchar2(1000);
begin
  lv_sqlstr := 'select ' || ip_clob_column || ' from ' || ip_table_name ||
               ' where ' || ip_PK_column || '=' || ip_PK_value;
  EXECUTE IMMEDIATE lv_sqlstr
    INTO lv_clob;
  return lv_clob;
end;
/

create or replace procedure updateclob(ip_dest_table_name  in varchar2, 
                                       ip_dest_PK_column   in varchar2,
                                       ip_dest_PK_value    in number,  
                                       ip_dest_clob_column in varchar2,
                                       ip_src_table_name   in varchar2,
                                       ip_src_PK_column    in varchar2,
                                       ip_src_PK_value     in number,
                                       ip_src_clob_column  in varchar2)
is   
  lv_src_clob  clob;
  lv_dest_clob clob;   
  lv_amt       number;     
  lv_sqlstr    varchar2(1000);   
begin 
  lv_sqlstr := 'select ' || ip_dest_clob_column || ' from ' || ip_dest_table_name ||
         ' where ' || ip_dest_PK_column || '=' || ip_dest_PK_value || 'for update';
  EXECUTE IMMEDIATE lv_sqlstr
    INTO lv_dest_clob; 
  lv_src_lob := getclob(ip_src_table_name,ip_src_PK_column,ip_src_PK_value,ip_src_clob_column);
  lv_amt := dbms_lob.getlength(lv_src_lob);
  dbms_lob.copy(lv_dest_clob,lv_src_lob,lv_amt);
  commit;
end;
/

Here is an example for inserting clob

SQL> select * from t_src_clob;
 
        NO CONTENT
---------- --------------------------------------------------------------------------------
         1 xxx
         2 yyy
SQL> select * from t_dest_clob;
 
        NO CONTENT
---------- --------------------------------------------------------------------------------
 
SQL> insert into t_dest_clob values(5,'ooo');
 
1 row inserted
 
SQL> commit;
 
Commit complete

SQL> select * from t_dest_clob;
 
        NO CONTENT
---------- --------------------------------------------------------------------------------
        3

SQL> exec updateclob('t_dest_clob','no',3,'content','t_src_clob','no',1,'content');
 
PL/SQL procedure successfully completed
 
SQL> select * from t_src_clob;
 
        NO CONTENT
---------- --------------------------------------------------------------------------------
         3 xxx