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(?????)

    Requires Free Membership to View

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 first published in April 2009

There are Comments. Add yours.

 
TIP: Want to include a code block in your comment? Use <pre> or <code> tags around the desired text. Ex: <code>insert code</code>

REGISTER or login:

Forgot Password?
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
Sort by: OldestNewest

Forgot Password?

No problem! Submit your e-mail address below. We'll send you an email containing your password.

Your password has been sent to: