Re: Problems with legacy pl/sql ( sql really ) code

Steve wrote:

> 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 ).

The cursor loop is as you have determined unnecessary.

I doubt it is you code that is unreliable rather the connection. Talk to your network people and be sure to test the dblink as part of your code.

BEGIN
   SELECT COUNT(*)
   INTO i
   FROM user_tables@<remote_db>;
EXCEPTION
   WHEN OTHERS THEN
     .. bail out gracefully;
END;

-- 
Daniel A. Morgan
University of Washington
damorgan_at_x.washington.edu
(replace 'x' with 'u' to respond)