Re: Update multiple tables in different schema

From: Mark D Powell <Mark.Powell_at_eds.com>
Date: Wed, 6 Feb 2008 07:45:36 -0800 (PST)
Message-ID: <638421b5-852c-4868-b332-4b1935075976@k39g2000hsf.googlegroups.com>


On Feb 6, 9:01 am, writetoor..._at_gmail.com wrote:
> 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.

Try placing an update trigger on the production table so that when the update is done the trigger propogates the update to the ar version of the table. Only one or the other table will actually be updated by the statement will be applied to both tables this way.

HTH -- Mark D Powell -- Received on Wed Feb 06 2008 - 09:45:36 CST

Original text of this message