Re: Materialized View conflict with VPD => ORA-30372:, please help..

On Feb 20, 3:09 pm, krisl..._at_gmail.com wrote:
> Hi gurus,
>
> Finally I can get the Materializde View works with REFRESH FAST ON
> COMMIT and UNION ALL.
>
> But I have another problem when testing it by entering a transaction
> which is one of the base tables of the MV. The problem is I also
> implement VPD on the table. So I get Error :
>
> # JBO-26065: Error during commit.
> # ORA-12008: error in materialized view refresh path ORA-30372: fine
> grain access policy conflicts with materialized view.
>
> Solution from oracle docs is to return null on the policy function.
> But the problem is : from within my policy function (attached below) ,
> how can I determine that the query is done by MV refresh process so
> that I can return null ??
>
> Thank you for your help,
> Krist
>
> PS. Here is my policy function :
> FUNCTION vpd_predicate(schema_name IN VARCHAR2,object_name IN
> VARCHAR2)
> RETURN VARCHAR2
> IS
> lv_predicate VARCHAR2(1000):='';
> BEGIN
> IF sys_context('vpd_test','user_type') = 'nasional' THEN
> lv_predicate:=''; -- allow all access
>
> ELSIF sys_context('vpd_test','user_type') = 'branch'
> THEN
> lv_predicate:='branch_code='''||
> sys_context('vpd_test','locgrp_code')||'''';
>
> ELSIF sys_context('vpd_test','user_type') = 'area' THEN
> lv_predicate:=branch_code='''||
> sys_context('vpd_test','loc_code')||'''';
>
> ELSE
> lv_predicate:='1=2'; -- block access
> END IF;
> RETURN lv_predicate;
> END;
Add this:

IF SYS.DBMS_MVIEW.I_AM_A_REFRESH THEN
  RETURN NULL;
END IF; at the very beginning of the policy function before other IF blocks. This should do the trick.

Regards,

   Vladimir M. Zakharychev
   N-Networks, makers of Dynamic PSP(tm)    http://www.dynamicpsp.com Received on Wed Feb 20 2008 - 06:42:34 CST