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

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 Received on Wed Feb 20 2008 - 06:56:24 CST