Update multiple tables in different schema

Hi,

I have two database schemas, a production schema pr and an archival schema ar. The same table exists in both the schema and the older records of the table are moved by an application to the archive (ar) schema from the production (pr) schema. So, a record will either exist in the table in pr schema or in the same table in ar schema. Now, for a very rare case I need to update certain non Key column(s) of the table with a sql like -

update table t set colA = 'X' where ColA = 'B';

and I need to update both the tables in pr as well as ar schema. So, I planned to create a view (v) like this -

create view V as
select <cols> from pr.t
union all
select <same cols> from ar.t;

I thought I will be able to update the records of the unified updatable view with the original update sql but I got an ora-01732 error (data manipulation operation not legal on this view) while executing it. I checked that the columns are not updatable from the user_updatable_columns view. I guess Oracle (10.2.0.2) doesn't allow union all clause in the updatable view even if the records are unique from each of the data sources.

I can't fire multiple update statements on two different tables (on two different schema) because there is a standard application (which we can't change) that generates such update sqls for more than 500 tables. We have separated the data into a production and Archive for performance & storage reasons and now we want to propagate the update changes from the standard application by silently creating the views on which the standard application can transact.

Could anyone suggest a better way to avoid the error or to update the records in the problem context?

Any suggestion is welcome. Received on Wed Feb 06 2008 - 08:01:58 CST