Re: Cursor LOOP and Clob reinitializing

From: Thomas J. Kyte <tkyte_at_us.oracle.com>
Date: 2000/05/17
Message-ID: <8fsq4u$h31$1@nnrp1.deja.com>#1/1

In article <8fs6gu$1moj$1_at_news4.isdnet.net>,   "Jean-Michel Scheiwiler" <scjm_at_cybercable.fr> wrote:
> I've got 2 tables: MRF (id,clob_textR) and MXF(id,id2,clob_textX)
> Given 1 id in the MRF table, i want to append all the clob_text of
 MXF to
> the one in MRF:
>
> Example:
> MRF
> 1 yada
> 2 gee
> MXF
> 1 1 yidi
> 1 2 go
> 2 1 cool
> 2 2 big
>
> =>PROCESS=>
> MRF
> 1 yada yidi go
> 2 gee cool big
>
> I've got a cursor CCOM (for MXF):
>
> flagfirst:='O';
> FOR rec IN CCOMM LOOP
>
> IF flagfirst = 'O' THEN
> idT := rec.id;
> SELECT Clob_TextT INTO clob_tempo FROM MRF WHERE id=rec.id FOR
> UPDATE;
> flagfirst := 'N';
> END IF;
>
> IF idT != rec.id THEN
> UPDATE MRF SET Clob_TextT = clob_tempo WHERE id = rec.id ;
> NumT := rec.NumLieu;
> SELECT Clob_TextT INTO clob_tempo FROM MRF WHERE id=rec.id FOR
 UPDATE;
> END IF;
>
> dbms_lob.append(clob_tempo, rec.Clob_textX);
>
> END LOOP;
>
> The problem is that the process keep appending data from the
 beginning:
> => MRF
> 1 yada yidi go
> 2 yada yidi go gee cool big
>
> How can I reinitialize the clob_tempo when the id change ? (i've made
 some
> tries but without any result )
> Or is there another method to solve this problem ?
>
> Thank you for any help
>
>

ops$tkyte_at_8i> create table mrf ( id int primary key, data clob ); Table created.

ops$tkyte_at_8i> create table mxf ( id int, seq int, data clob, constraint mxf_pk primary key(id,seq) );

Table created.

ops$tkyte_at_8i> insert into mrf values ( 1, 'yada' ); ops$tkyte_at_8i> insert into mrf values ( 2, 'gee' );

ops$tkyte_at_8i> insert into mxf values ( 1, 1, 'yidi' );
ops$tkyte_at_8i> insert into mxf values ( 1, 2, 'go' );
ops$tkyte_at_8i> insert into mxf values ( 2, 1, 'cool' );
ops$tkyte_at_8i> insert into mxf values ( 2, 2, 'big' );

ops$tkyte_at_8i> select * from mrf;

        ID DATA

---------- --------------------
         1 yada
         2 gee

ops$tkyte_at_8i> begin
  2      for x in ( select * from mrf ) loop
  3          for y in ( select * from mxf where id = x.id order by
seq ) loop
  4              dbms_lob.writeappend( x.data, 1, ' ' );
  5              dbms_lob.append( x.data, y.data );
  6          end loop;
  7      end loop;

  8 end;
  9 /

PL/SQL procedure successfully completed.

ops$tkyte_at_8i> select * from mrf;

        ID DATA

---------- --------------------
         1 yada yidi go
         2 gee cool big


--
Thomas Kyte                              tkyte_at_us.oracle.com

Oracle Service Industries
http://osi.oracle.com/~tkyte/index.html

--
Opinions are mine and do not necessarily reflect those of Oracle Corp


Sent via Deja.com http://www.deja.com/
Before you buy.

Received on Wed May 17 2000 - 00:00:00 CDT