ORA-28113 Policy Predicate Has Error Even When The VPD Function is Flawless (Doc ID 782462.1)

Last updated on APRIL 20, 2015

Applies to:

Oracle Server - Enterprise Edition - Version 8.1.7.4 to 11.1.0.6 [Release 8.1.7 to 11.1]
Information in this document applies to any platform.
Oracle Server Enterprise Edition - Version: 10.2.0.3
***Checked for relevance on 26-Sep-2012***

Symptoms

A query running on a table with a VPD function attached keeps failing with ORA-28113, despite the fact that the function does not have any syntactical error.
The syntactically incorrect VPD piece that is reported by the error cannot be found inside the VPD function text, even more, recreating the VPD function (or package) does not solve the problem.

Consider the following scenario:

DROP TABLE test;

CREATE TABLE test
(id NUMBER,
user_name VARCHAR2(30));

INSERT INTO test VALUES(1,'ADMIN');
INSERT INTO test VALUES(2,'TEST');
COMMIT;

CREATE OR REPLACE PACKAGE test_vpd
AS
    FUNCTION return_user(object_schema in varchar2,object_name varchar2) 
       RETURN VARCHAR2;
END test_vpd;
/

CREATE OR REPLACE PACKAGE BODY test_vpd
AS
    FUNCTION return_user(object_schema in varchar2,object_name varchar2)  
       RETURN VARCHAR2
    AS
    BEGIN
       RETURN 'user_name = (SYSCONTEXT(''userenv'',''current_user''))';
    END return_user;
END test_vpd;
/

BEGIN
    DBMS_RLS.add_policy
(object_schema => 'admin',
object_name     => 'test',
policy_name     => 'test_vpd_policy',
function_schema => 'admin',
policy_function => 'test_vpd.return_user',
statement_types => 'select',
policy_type     => DBMS_RLS.STATIC
);
END;
/

(the user creating all these objects is ADMIN, for the sake of demonstrating that the VPD works in the end.
Now, when running:

SQL>  SELECT * FROM TEST;
 SELECT * FROM TEST
               *
ERROR at line 1:
ORA-28113: policy predicate has error

After fixing the code and replacing SYSCONTEXT with the correct SYS_CONTEXT syntax, the error is still there, even if the package has been syntactically fixed.
Even after dropping the policy, recreating the package and recreating the policy, the error still does not disappear.

Cause

Sign In with your My Oracle Support account

Don't have a My Oracle Support account? Click to get started

My Oracle Support provides customers with access to over a
Million Knowledge Articles and hundreds of Community platforms