Daniel Morgan wrote:
> 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;
>
Hi Daniel,
Thanks for that. However, when I try and use this code, I get an error
message about updating a non-nullable field to null, even though the id
column in the three tabls contains the same set of values.
Any ideas?
steve
Received on Sun Sep 19 2004 - 13:54:41 CDT
