Two VARCHARs -> One CLOB

[cwsstins]

I have two VARCHAR2 columns (V2_01, V2_02) that are being phased out of the application. They are to be replaced by one CLOB (C_01). I need to write a script to take the data from V2_01 and V2_02 and load it into C_01, preferably with a carriage return separate the two values.

For instance:
V2_01 = 'Comment data for first Notes field'
V2_02 = 'More comment data - second Notes field'

C_01 should =
'Comment data for first Notes field
More comment data - second Notes field'

I don't know where to start with this...is there some kind of APPEND statement, or do I need a CONCAT? Any help appreciated...

 

[Schoover]

simple example to get you started hopefully.

UPDATE mytable
SET mytable_clob = EMPTY_CLOB()
where mytable_id = id_in;

commit;

--now lock the row so we can write the CLOB
 begin
select mytable_clob into mytable_clob
             from mytable
             where mytable_id = id_in
             for UPDATE;

DBMS_LOB.WRITE(mytable_clob, length(v1_01), 1, v1_01);
DBMS_LOB.WRITEAPPEND(mytable_clob, 3, "whatever crlf is..."  );
DBMS_LOB.WRITEAPPEND(mytable_clob, length(v2_02), 1, v2_02);

off the top of my head its something like that...

 

[cwsstins]

Thanks for the response...I actually went with:

UPDATE Tbl
SET C_01 = V2_01|| ' ' || V2_02
WHERE V2_01 is not null or V2_02 is not null;