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

From: Steve <ThisOne_at_Aint.valid>
Date: Mon, 20 Sep 2004 18:58:48 +1200
Message-ID: <cilv39$4j3$1@lust.ihug.co.nz>

Daniel Morgan wrote:

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

As far as I can tell, the t0 identifier in the update line is not being carried across into the select being used to populate update variables. I've simplified the sql down to ...

update table0 t0
set
(

         t0.col1
)
=
(

         select
                 t1.col1
         from
                 table1 t1,
                 table2 t2
         where
                 t1.id = t2.id
         and     t2.status = 'OK'
         and     t0.id = t1.id
         and     t1.com_id is not null

);

gives this error...

update table0 t0
*
ERROR at line 1:
ORA-01407: cannot update ("REMOTE_DATABASE"."TABLE0"."COL1") to NULL ORA-02063: preceding line from REMOTE_DATABASE

I tried removing the braces around the
select statement, and it then didn't recognise the label t0 in the where clause.

(ORA-00904 "T0"."ID" invalid identifier )

Is there something fundamentally wrong in what I'm trying to do? col1 in all 3 tables contains the same set of identifiers, and is not nullable. t0 is a dblink to a remote database ( on the same server while testing ).

Any suggestions would be gratefully received, little hair left, very grey!

Cheers,

Steve Received on Mon Sep 20 2004 - 01:58:48 CDT