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

2 comments:

Jeffrey Kemp said...

Interesting. Does it still give an error if the view doesn't do an ordinary join, e.g.:

UPDATE
(SELECT r.type_code
,(
SELECT a.standard_rate
FROM train.resource_rates a
WHERE a.type_code = r.type_code
) AS standard_rate
,r.code
,r.daily_rate
FROM train_apex.resources r
)
SET daily_rate = standard_rate;

Scott Wesley said...

Ahh, the scalar subquery wins again!

While it executed successfully with no complaints from the bouncers, that particular statement also updated every row with no rates to null.