Answer

How to concatenate rows into a single CLOB in PL/SQL

PL/SQL expert Dan Clamage explains how to concatenate rows into a single CLOB in PL/SQL.

Dan Clamage
By
Published: 16 Apr 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(?????)
I'm assuming you just want one row in table A, whose CLOB is the concatenation of a series of rows in B? Here is one approach.

I'd suggest you break the problem down into simpler units. One issue is how to concatenate the text values into a single CLOB. I wrote a stand-alone function to do just that:

create or replace
function assemble_clob(p_from in number, p_to in number)
return clob
is
  v_clob clob;  -- inner one
begin
  for rec in (
      select keys, text 
      from b 
      where keys between p_from and p_to)
  loop
    if (rec.keys = p_from) then  -- first item
      v_clob := rec.text;
    else  -- subsequent item
      v_clob := v_clob || CHR(10) || rec.text;  -- prepend newline
    end if;
  end loop;
  return (v_clob);
end assemble_clob;
/
Now I can use this in SQL: 

insert into a (keys, text) values (1, assemble_clob(1, 3));

Notice how this simplifies the insert statement.

When I query table A, I get: 

SQL> column text format a30
SQL> select * from a;

      KEYS TEXT
---------- ------------------------------
         1 How do I
             merge multiple rows
               into one row of type CLOB?

Dig Deeper on Oracle development languages