Q

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.

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?
This was last published in April 2009

Dig Deeper on Using Oracle PL-SQL

PRO+

Content

Find more PRO+ content and other member only offers, here.

Have a question for an expert?

Please add a title for your question

Get answers from a TechTarget expert on whatever's puzzling you.

You will be able to add details on the next page.

Start the conversation

Send me notifications when other members comment.

By submitting you agree to receive email from TechTarget and its partners. If you reside outside of the United States, you consent to having your personal data transferred to and processed in the United States. Privacy

Please create a username to comment.

-ADS BY GOOGLE

SearchDataManagement

SearchBusinessAnalytics

SearchSAP

SearchSQLServer

TheServerSide

SearchDataCenter

SearchContentManagement

SearchFinancialApplications

Close