Re: Update statement using a join on a materialized view

Andy wrote:
> Hi,
>
> Hopefully someone can help me. I have 2 tables and 1 materialized view
> which is updated from a different database instance, the materialized
> view links the two other tables. So for example
>
> Table_A
> ------------
>
> unique_key_A
> table_A_parameter
>
> Table_B
> ------------
> unique_key_B
> table_B_parameter
>
>
> Materialized_View_C
> ------------------------------
> unique_key_C
> unique_key_A
> unique_key_B
> other_stuff
>
> I need to update records in Table A based on the value of
> table_B_parameter in Table B, the statement I would normally use for
> this would be:
>
> update (
> select table_A_parameter as param_to_update
> from Table_A, Table_B, Materialized_View_C
> where Table_A.unique_key_A = Materialized_View_C.unique_key_A
> and Materialized_View_C.unique_key_B = Table_B.unique_key_B
> and Table_B.table_B_parameter = 'X'
> ) set param_to_update = Y;
>
> however whenever i do this I get a ORA-01031: insufficient privileges
> error I assume as oracle thinks i'm trying to update the materialized
> view, but i'm not ...
>
> any ideas on how to do this kind of update ? can I grant update on the
> materialized view ?
>
> any answers appreciated
>
> Cheers
>
> Andy

Is this happening in SQL or in PL/SQL?
Does your Oracle installation have a version number?

Likely you are in a PL/SQL procedure and the privilege on the materialized view has been granted through a role: It must be explicit.

-- 
Daniel A. Morgan
http://www.psoug.org
damorgan_at_x.washington.edu
(replace x with u to respond)