Wednesday, 4 April 2012

Updating key-preserved inline views

I learnt something today - there is a scenario where you need update privileges on a table you aren't updating.

Here is my example - I have a table TRAIN_APEX.RESOURCES that I would like to update based on TRAIN.RESOURCE_RATES. So the key factor here is my source table is in a different schema to my destination table - and I only have select privileges on resource_rates.
TRAIN_APEX> SELECT privilege, grantee, table_name FROM all_tab_privs where table_name = 'RESOURCE_RATES';

PRIVILEGE            GRANTEE              TABLE_NAME
-------------------- -------------------- --------------------------------------------------------
SELECT               TRAIN_APEX           RESOURCE_RATES

1 row selected.
This means when I attempt to run an update that uses an elegant key-preserved in-line view, I can't do it!
TRAIN_APEX> UPDATE
  2    (SELECT r.type_code
  3           ,a.standard_rate
  4           ,r.code
  5           ,r.daily_rate
  6     FROM train_apex.resources r
  7         ,train.resource_rates a
  8     WHERE a.type_code = r.type_code)
  9  SET daily_rate = standard_rate;
       ,train.resource_rates a
              *
ERROR at line 7:
ORA-01031: insufficient privileges
I've had a look through the documentation for views and update statements, and I can't see it as a pre-requisite.

Out of curiosity I defined the statement as an actual view to confirm the base table is key-preserved. I knew this already because I've successfully run this statement when the tables are in the same schema, or in scenarios where my account has UPDATE ANY TABLE.
TRAIN_APEX> CREATE VIEW resources_vw AS
  2  SELECT r.type_code
  3        ,a.standard_rate
  4        ,r.code
  5        ,r.daily_rate
  6  FROM train_apex.resources r
  7      ,train.resource_rates a
  8  WHERE a.type_code = r.type_code;

View created.

Elapsed: 00:00:00.05
SQL>
SQL> @view_dml resources_vw

COLUMN_NAME          INSERTABL UPDATABLE DELETABLE
-------------------- --------- --------- ---------
TYPE_CODE            YES       YES       YES
STANDARD_RATE        NO        NO        NO
CODE                 YES       YES       YES
DAILY_RATE           YES       YES       YES

4 rows selected.
So instead, I have to run a "normal" update with a condition to check for presence of rows in my source table, which also means my update isn't as efficient.
UPDATE train_apex.resources r
SET daily_rate =
  (SELECT standard_rate
   FROM train.resource_rates a
   WHERE a.type_code = r.type_code)
WHERE EXISTS
  (SELECT NULL
   FROM train.resource_rates a
   WHERE a.type_code = r.type_code)
Do you think this is a bug or expected behaviour?

Scott
Post a Comment