QUESTION POSED ON: 16 April 2009
How would I merge multiple varchar2 rows into one row of type CLOB?
Example:
create table A (keys number, text CLOB);
create table B (keys number, text varchar2(100));
insert into B values (1, 'How do I ');
insert into B values (2, ' merge multiple rows ');
insert into B values (3, ' into one row of type CLOB? ');
After inserting from table B into table A, I would like to see the following after selecting from table A:
keys text
----- -------
1 How do I
merge multiple rows
into one row of type CLOB?
I wrote the following, but am unsure of how to utilize dbms_lob.writeAppend() with it.
DECLARE
TYPE A_table_type IS TABLE OF A%ROWTYPE
INDEX BY PLS_INTEGER;
t_A A_table_type;
CURSOR c1
SELECT text FROM B WHERE keys BETWEEN 1 AND 3;
BEGIN
OPEN c1;
LOOP --Fetch a configured set of rows at once
FETCH c1
BULK COLLECT INTO t_A LIMIT l_ROW_LIMIT;
EXIT WHEN t_A.COUNT = 0;
--For each set of rows fetched...
FOR x IN 1 .. t_A.COUNT LOOP
dbms_lob.writeAppend(?????)
|
