Re: Materialized View conflict with VPD => ORA-30372:, please help..
Date: Wed, 20 Feb 2008 04:42:34 -0800 (PST)
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,
> PS. Here is my policy function :
> FUNCTION vpd_predicate(schema_name IN VARCHAR2,object_name IN
> RETURN VARCHAR2
> lv_predicate VARCHAR2(1000):='';
> IF sys_context('vpd_test','user_type') = 'nasional' THEN
> lv_predicate:=''; -- allow all access
> ELSIF sys_context('vpd_test','user_type') = 'branch'
> ELSIF sys_context('vpd_test','user_type') = 'area' THEN
> lv_predicate:='1=2'; -- block access
> END IF;
> RETURN lv_predicate;
IF SYS.DBMS_MVIEW.I_AM_A_REFRESH THEN
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 Received on Wed Feb 20 2008 - 06:42:34 CST