Re: Materialized View conflict with VPD => ORA-30372:, please help..
Date: Wed, 20 Feb 2008 04:56:24 -0800 (PST)
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.
> Vladimir M. Zakharychev
> N-Networks, makers of Dynamic PSP(tm)
> http://www.dynamicpsp.com- Hide quoted text -
> - Show quoted text -
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