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

From: <krislioe_at_gmail.com>
Date: Wed, 20 Feb 2008 04:09:10 -0800 (PST)
Message-ID: <2e3c6e1e-94f6-4be2-860e-17956068b628@28g2000hsw.googlegroups.com>

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;

Received on Wed Feb 20 2008 - 06:09:10 CST