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

On Feb 20, 3:56 pm, krisl..._at_gmail.com wrote:
> On Feb 20, 7:42 pm, "Vladimir M. Zakharychev"
>
>
>
> <vladimir.zakharyc..._at_gmail.com> wrote:
> > 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-Hide quoted text -
>
> > - Show quoted text -
>
> Hi Vladimir,
> Thanks for your reply.
>
> When I add it to my policy function and recompile I get this error :
> PLS-00201: identifier 'SYS.DBMS_MVIEW' must be declared
>
> What should I do ?
>
> Thank you very much,
> Krist

Sorry, my bad, remove the SYS. prefix. DBMS_MVIEW is a PUBLIC synonym. :)

Regards,

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