Home » SQL & PL/SQL » SQL & PL/SQL » merge multiple varchar2 rows into one row of type CLOB
merge multiple varchar2 rows into one row of type CLOB [message #393613] Mon, 23 March 2009 10:52 Go to next message
JSF0
Messages: 3
Registered: March 2009
Junior Member
There is something I am not understanding. How would I merge multiple varchar2 rows into one row of type CLOB though?

After inserting from table B into table A, I would like to see the following after selecting from table A:


keys  text
----- -------
1     >TEXT<
      How do I
        merge multiple rows
          into one row of type CLOB?
      >/TEXT<


I wrote the following, but am unsure of how to utilize dbms_lob.writeAppend() with it:

create table A (keys number, text CLOB);
create table B (keys number, text varchar2);

insert into B values ('1', ">TEXT<");
insert into B values ('2', "How do I");
insert into B values ('3', "  merge multiple rows"); 
insert into B values ('4', "    into one row of type CLOB?");
insert into B values ('5', ">/TEXT<");

DECLARE
TYPE A_table_type IS TABLE OF A%ROWTYPE
INDEX BY BINARY_INTEGER;
  
t_A A_table_type;

CURSOR c1 IS
SELECT text FROM B WHERE keys BETWEEN 1 AND 5;
  BEGIN
  OPEN c1; 
    LOOP --Fetch a configured set of rows at once
        FETCH c1
        BULK COLLECT INTO t_A LIMIT 100;
          
        EXIT WHEN t_A.COUNT = 0;
          --For each set of rows fetched...
          FOR x IN 1 ..  t_A.COUNT LOOP
            NULL; --dbms_lob.writeAppend(?????);
          END LOOP;
    END LOOP;
  CLOSE c1;
  END;

Re: Multiple records in one row [message #393628 is a reply to message #393613] Mon, 23 March 2009 13:04 Go to previous messageGo to next message
BlackSwan
Messages: 25050
Registered: January 2009
Location: SoCal
Senior Member
truncate table B;
insert into B values ('1', ">TEXT<");
insert into B values ('10', ">/TEXT<");
insert into B values ('2', "How do I");
insert into B values ('9', "    into one row of type CLOB?");
insert into B values ('3', "  merge multiple rows"); 
insert into B values ('8', "  merge multiple rows"); 
insert into B values ('4', "    into one row of type CLOB?");
insert into B values ('7', "How do I");
insert into B values ('5', ">/TEXT<");
insert into B values ('6', ">TEXT<");


How do you decide which rows go in what order into table A?

Rows in a table have no inherent order & are like balls in a basket.
Re: Multiple records in one row [message #393629 is a reply to message #393628] Mon, 23 March 2009 13:36 Go to previous messageGo to next message
JSF0
Messages: 3
Registered: March 2009
Junior Member
Good catch. The tweaked code is below. I am still unsure if I am going in the right direction. I am trying to get it running in a proc, while figuring out how to use dbms_lob -if it is even appropriate.

create table A (keys number, text CLOB);
create table B (keys number, text varchar2(80));

insert into B values ('1', ">TEXT<");
insert into B values ('2', "How do I");
insert into B values ('3', " merge multiple rows"); 
insert into B values ('4', " into one row of type CLOB?");
insert into B values ('5', ">/TEXT<");

DECLARE
TYPE A_table_type IS TABLE OF A%ROWTYPE
INDEX BY BINARY_INTEGER;

t_A A_table_type;

CURSOR c1 IS
SELECT *
FROM B 
WHERE keys BETWEEN 1 AND 5
ORDER BY keys;

BEGIN
  OPEN c1; 
    LOOP
        FETCH c1
        BULK COLLECT INTO t_A LIMIT 100;

        EXIT WHEN t_A.COUNT = 0;

          FOR x IN 1 .. t_A.COUNT LOOP
           dbms_lob.writeAppend(l_blob??, LENGTH(t_A.text(x)), t_A.text(x));
           dbms_lob.writeAppend( l_blob??, 1, '0A' ); --newline
          END LOOP;
    END LOOP;
  CLOSE c1;
END;
Re: Multiple records in one row [message #393630 is a reply to message #393629] Mon, 23 March 2009 13:52 Go to previous messageGo to next message
Michel Cadot
Messages: 64152
Registered: March 2007
Location: Nanterre, France, http://...
Senior Member
Account Moderator
So many errors in your code (almost at each line, see --------------):
SQL> DECLARE
  2  TYPE B_table_type IS TABLE OF B%ROWTYPE  ------------------
  3  INDEX BY BINARY_INTEGER;
  4  
  5  t_B B_table_type;                        ------------------
  6  
  7  CURSOR c1 IS
  8  SELECT *
  9  FROM B 
 10  WHERE keys BETWEEN 1 AND 5
 11  ORDER BY keys;
 12  
 13  l_blob clob;                            -------------------
 14  
 15  BEGIN
 16  
 17    insert into a values (0,empty_clob()) returning text into l_blob;  --------------------
 18  
 19    OPEN c1; 
 20      LOOP
 21          FETCH c1
 22          BULK COLLECT INTO t_B LIMIT 100;  ----------------------
 23  
 24          EXIT WHEN t_B.COUNT = 0;          ----------------------
 25  
 26            FOR x IN 1 .. t_B.COUNT LOOP    ----------------------
 27             dbms_lob.writeAppend(l_blob, LENGTH(t_B(x).text), t_B(x).text); --------------------
 28             dbms_lob.writeAppend( l_blob, 1, '0A' ); --newline
 29            END LOOP;
 30      END LOOP;
 31    CLOSE c1;
 32  END;
 33  /

PL/SQL procedure successfully completed.

SQL> select * from a;
      KEYS
----------
TEXT
-----------------------------------------------------------------------------
         0
>TEXT<0How do I0 merge multiple rows0 into one row of type CLOB?0>/TEXT<0

1 row selected.

Regards
Michel
Re: Multiple records in one row [message #393738 is a reply to message #393629] Tue, 24 March 2009 04:15 Go to previous messageGo to next message
JRowbottom
Messages: 5933
Registered: June 2006
Location: Sunny North Yorkshire, ho...
Senior Member
I'd do something like:
DECLARE
  CURSOR c1 IS
    SELECT *
    FROM B 
    WHERE keys BETWEEN 1 AND 5
    ORDER BY keys;

  v_clob    clob;
BEGIN
  FOR rec in c1 loop
    v_clob := v_clob||to_clob(rec.text);
  END LOOP;

  insert into a values (1,v_clob);
END;


In 10g, Oracle automatically bulk fetches cursors with an array size of 100, so there's no real need to use explicit bulk fetches except in odd circumstances where you want to fetch <100 rows.
Re: Multiple records in one row [message #393822 is a reply to message #393630] Tue, 24 March 2009 09:06 Go to previous message
JSF0
Messages: 3
Registered: March 2009
Junior Member
Impressive. Thanks. Sorry for all the errors. I know it is hardly fun dealing with newbies. BTW -I replaced:

dbms_lob.writeAppend( l_blob, 1, '0A' ); --newline

with

dbms_lob.writeAppend( l_blob, 1, chr(10)||chr(13) ); --newline

and it works the way I want it to on UNIX and Windows. merci.
Previous Topic: Grant References to Role ?
Next Topic: problem in 'IN' clause of select query
Goto Forum:
  


Current Time: Sat Dec 10 08:51:38 CST 2016

Total time taken to generate the page: 0.25521 seconds