Steve wrote:
> 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
Believe the error message as that is exactly what you are trying to do.
Given that I don't have your schema and code here I can'f fix the
problem for you but it shouldn't be that hard to modify your code to
do an insert into a "test" table and then compare which columns are
null and yet, in the original table, are not nullable.
Be sure your test table has no constraints of any type.
--
Daniel A. Morgan
University of Washington
damorgan_at_x.washington.edu
(replace 'x' with 'u' to respond)
Received on Sun Sep 19 2004 - 19:21:24 CDT
