VPD PROFILE Error

[namitsinghi]

Hi,

I have defined a VPD for Update/Insert/Delete/Read/Readwriteaccess .
But still I am getting following error when trying to insert a new record.

glog.util.exception.CreateExceptionWrapperjava.sql .SQLException: ORA-28115: policy with check option violationjava.sql.SQLException: ORA-28115: policy with check option violation at oracle.jdbc.dbaccess.DBError.throwSqlException(DBE rror.java:134) at oracle.jdbc.ttc7.TTIoer.processError(TTIoer.java:2 89) at oracle.jdbc.ttc7.Oall7.receive(Oall7.java:589) at oracle.jdbc.ttc7.TTC7Protocol.doOall7(TTC7Protocol .java:1957) at oracle.jdbc.ttc7.TTC7Protocol.parseExecuteFetch(TT C7Protocol.java:1114) at oracle.jdbc.driver.OracleStatement.executeNonQuery (OracleStatement.java:2181) at oracle.jdbc.driver.OracleStatement.doExecuteOther( OracleStatement.java:2063) at oracle.jdbc.driver.OracleStatement.doExecuteWithTi meout(OracleStatement.java:2905) at oracle.jdbc.driver.OraclePreparedStatement.execute Update(OraclePreparedStatement.java:644) at weblogic.jdbc.wrapper.PreparedStatement.executeUpd ate(PreparedStatement.java:147) at glog.util.jdbc.SqlUpdate.executeUpdate(SqlUpdate.j ava:133) at glog.util.jdbc.SqlUpdate.resetArguments(SqlUpdate. java:111) at glog.util.jdbc.SqlUpdate.execute(SqlUpdate.java:60 ) at glog.util.remote.BeanManagedEntityBean.executeUpda te(BeanManagedEntityBean.java:793) at glog.util.remote.BeanManagedEntityBean$1.execute(B eanManagedEntityBean.java:195) at glog.util.remote.BeanManagedEntityBean.dbModify(Be anManagedEntityBean.java:1066) at glog.util.remote.BeanManagedEntityBean.doCreate(Be anManagedEntityBean.java:191) at glog.util.remote.BaseEntityBean$1.doIt(BaseEntityB ean.java:460) at glog.util.remote.BaseEntityBean.ejb(BaseEntityBean .java:803) at glog.util.remote.BaseEntityBean.ejbCreator(BaseEnt ityBean.java:45 at glog.ejb.orderbase.db.ObOrderBaseBeanDB.ejbCreate( ObOrderBaseBeanDB.java:109) at glog.ejb.orderbase.ejb_ObOrderBase_3uirki_Impl.ejb Create(ejb_ObOrderBase_3uirki_Impl.java:195) at jrockit.reflect.VirtualNativeMethodInvoker.invoke( Ljava.lang.Object;[Ljava.lang.ObjectLjava.lang.Object;(Unknown Source) at java.lang.reflect.Method.invoke(Ljava.lang.Object;[Ljava.lang.Object;I)Ljava.lang.Object;(Unknown Source) at weblogic.ejb20.manager.ExclusiveEntityManager.crea te(ExclusiveEntityManager.java:750) at weblogic.ejb20.manager.ExclusiveEntityManager.remo teCreate(ExclusiveEntityManager.java:721) at weblogic.ejb20.internal.EntityEJBHome.create(Entit yEJBHome.java:249) at glog.ejb.orderbase.ejb_ObOrderBase_3uirki_HomeImpl .create(ejb_ObOrderBase_3uirki_HomeImpl.java:220) at jrockit.reflect.VirtualNativeMethodInvoker.invoke( Ljava.lang.Object;[Ljava.lang.ObjectLjava.lang.Object;(Unknown Source) at java.lang.reflect.Method.invoke(Ljava.lang.Object;[Ljava.lang.Object;I)Ljava.lang.Object;(Unknown Source) at glog.util.remote.BeanBaseInvoker.invoke(BeanBaseIn voker.java:24) at glog.util.remote.EntityBeanHomeInvoker.create(Enti tyBeanHomeInvoker.java:53) at glog.util.persistence.EjbPersistenceExecutor.inser tPerformed(EjbPersistenceExecutor.java:104)
Please let me know if there is some problem with this access or its something else, I am able to read the records fine, During search record is getting filtered, problem comes only when we are trying to insert a new record:

Table: OB_ORDER_BASE

PREDICATE:
EXISTS (SELECT 1 FROM ob_line obl, LOCATION lc, LOCATION_CORPORATION lcr WHERE lc.LOCATION_GID = obl.SOURCE_LOCATION_GID AND obl.order_base_gid = OB_ORDER_BASE.order_base_gid AND lc.LOCATION_GID = lcr.LOCATION_GID AND lcr.CORPORATION_GID = SYS_CONTEXT('gl_user_ctx','CORPORATION_GID'))

[acuartero]

Re: VPD PROFILE Error

For the record you are attempting to insert does the predicate evaluate to true or false?

-AC

[namitsinghi]

Re: VPD PROFILE Error

Added the condition : (DOMAIN_NAME = SYS_CONTEXT('gl_user_ctx','domain_name')) OR <above query> for the Read/Write predicate and the issue is resolved. This this, I am able to insert new Order Base.

But now facing another issue: Inserting new Order Base manually works fine. But create new Order base by copy from another order base, get the error:
{? = call vpd.is_domain_writeable(?,?)=null}; inParams=[OB_ORDER_BASE, FWD]; outParamType=class java.lang.String
Checked function vpd.is_domain_writeable and determined that the issue is in select statement: v_select_stm. Dynamically generated v_select_stm is:

select 1 from domain where domain_name = 'FWD'
and (DOMAIN_NAME = SYS_CONTEXT('gl_user_ctx','domain_name')) OR EXISTS (SELECT 1 FROM ob_line obl, LOCATION_CORPORATION lcr WHERE lcr.LOCATION_GID = obl.SOURCE_LOCATION_GID AND obl.order_base_gid = OB_ORDER_BASE.order_base_gid AND lcr.CORPORATION_GID = SYS_CONTEXT('gl_user_ctx','CORPORATION_GID'))
and EXISTS ( SELECT 1 FROM domain_write_grants_made_v WHERE grantee_domain LIKE SYS_CONTEXT('gl_user_ctx','vpd_domain_name') AND domain_name LIKE grantor_domain AND table_name = 'OB_ORDER_BASE')

The above query will obviously fail. But not sure on how to modify the predicate such that query will work fine in Copy Order Base also. Help appreciated.