| Oracle FAQ | Your Portal to the Oracle Knowledge Grid | |
 |
 | |||
| Oracle FAQ | Your Portal to the Oracle Knowledge Grid | |
|
| |||
Home -> Community -> Usenet -> c.d.o.misc -> Re: Update view problem
A copy of this was sent to "Mr. Chow Wing Siu" <wschow_at_Comp.HKBU.Edu.HK>
(if that email address didn't require changing)
On 18 Nov 1999 23:38:04 GMT, you wrote:
>rtproffitt_at_my-deja.com wrote:
>> In my sample, I get an error message:
>> update bobv set x='new' where x='old'
>> *
>> ERROR at line 1:
>> ORA-01732: data manipulation operation not legal on this view
>> Can you be more specific?
>
>Yes. This is the correct error message which I get.
>
>
>> Could you not just simply run two updates?
>
>> Update t1_at_db1
>> set x='new'
>> where x='old';
>
>> Update t1_at_db2
>> set x='new'
>> where x='old';
>
>This is feasible but when I need to port to Visual Basic.
>How to specific two data source and field and update to
>two databases? That's why I need to find out the view
>to resolve my problem.
>
Oracle8.0 and up allows ANY view to be updated. You can code the logic to update in an INSTEAD of trigger. consider:
tkyte_at_8.0> create table t1 ( x int primary key, data varchar2(25) ); Table created.
tkyte_at_8.0> create table t2 ( y int primary key, data varchar2(25) ); Table created.
tkyte_at_8.0> create or replace view v ( a, b )
2 as
3 select * from t1
4 UNION ALL
5 select * from t2;
View created.
tkyte_at_8.0> insert into t1 values ( 5, null ); 1 row created.
tkyte_at_8.0> insert into t2 values ( 5, null ); 1 row created.
tkyte_at_8.0> update v set b = 'Hello World' where a = 5; update v set b = 'Hello World' where a = 5
*
ERROR at line 1:
ORA-01732: data manipulation operation not legal on this view
tkyte_at_8.0> create or replace trigger v_trigger
2 instead of UPDATE on v
3 for each row
4 begin
5 update t1 set x = :new.a, data = :new.b where x = :old.a; 6 update t2 set y = :new.a, data = :new.b where y = :old.a;7 end;
Trigger created.
tkyte_at_8.0>
tkyte_at_8.0> update v set b = 'Hello World' where a = 5;
2 rows updated.
tkyte_at_8.0>
tkyte_at_8.0> select * from v;
A B
---------- -------------------------
5 Hello World
5 Hello World
tkyte_at_8.0>
>
>> Or if timing is a problem, then do one at a time....
>> Create a list of the x's which are old,
>> then LOOP through the list doing the two
>> updates for each individual item in the list...
>> doing a COMMIT every 1 - n rows (as you see fit).
>
>> Also remember that your view is defined as UNION,
>> which will sort and remove duplicates...you may want
>> UNION ALL instead.
>
>> Robert Proffitt
>> Beckman Coulter
>> Brea, California
>
>
>Yes. It requires a COMMIT for each update (really necessary?).
>
No -- you don't need a commit for each update. you would update/update and then commit.
>Instead of updating to two databases for the same pair of data,
>the last resort is to:
>
> site 1 site 2
> Table A (view from site 2) Table A
> Table B Table B (view from site 2)
>
>Updating any data requires a two phase commit. But I ask
>if one site (for example site 2) is down, can site 1 work
>normally? I mean update/insert/delete Table A at site 1.
>
>Thanks for help.
--
See http://osi.oracle.com/~tkyte/ for my columns 'Digging-in to Oracle8i'...
Current article is "Part I of V, Autonomous Transactions" updated June 21'st
Thomas Kyte tkyte_at_us.oracle.com Oracle Service Industries Reston, VA USA
Opinions are mine and do not necessarily reflect those of Oracle Corporation Received on Fri Nov 19 1999 - 07:28:53 CST
 |
 |