Problems with legacy pl/sql ( sql really ) code

From: Steve <ThisOne_at_Aint.valid>
Date: Sun, 19 Sep 2004 22:05:53 +1200
Message-ID: <cijlm2$2en$1@lust.ihug.co.nz>

Hi folks,

I've got a problem sith a bit of code, and am wondering if there's any flaw on the logic I want to use to improve it.

This is what I've got...

for t0 in
(

         select id from table
                   where ...

)
loop
update table2
set
(

         column_list...
)
=
(

select

         column_list...
from table1 t1
where t2.id = t1.id
)
where t2.id=t0.id ;
commit;
end loop;

What I want to use is...

update table2
set
(

         column_list...
)
=
(

select

         column_list...
from table1 t1
where t2.id = t1.id
where t2.id in

     (
         select id from table
                   where ...
     )

);

The problem is that t2 is in a separate database from t0 and t1, and is now geographically separated, and this code has become unreliable.

Comments would be gratefully received. ( orginal code is Win 2k/8.1.7.4, currently on 2003/9.0.2, and just to be difficult I'm testing on RHEL3/10g - but in this case I don't *think* it matters ).

Cheers,

Steve
( Apologies for maybe not using the correct oracle newsgroup, but it's
the only one I subscribe to ). Received on Sun Sep 19 2004 - 05:05:53 CDT