Thank you very much
Jean-Michel
"Thomas J. Kyte" <tkyte_at_us.oracle.com> wrote in message
news:8fsq4u$h31$1_at_nnrp1.deja.com...
> 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
