ORA-28113: policy predicate has error


CREATE OR REPLACE PACKAGE jgottumu.scott_dept_context AS
  PROCEDURE set_dept;
END;
/

CREATE OR REPLACE PACKAGE BODY jgottumu.scott_dept_context AS
  PROCEDURE set_dept IS
  BEGIN
    DBMS_SESSION.SET_CONTEXT(
             'which_depts',
             'dept_no',
             20);
  END set_dept;
END;
/

CREATE CONTEXT which_depts USING scott_dept_context;
       
CREATE OR REPLACE PACKAGE dept_pkg AS
  FUNCTION secure(
    p_owner VARCHAR2,
    p_object VARCHAR2)
    RETURN VARCHAR2;
END;        

CREATE OR REPLACE PACKAGE BODY dept_pkg AS
  FUNCTION secure(
    p_owner VARCHAR2,
    p_object VARCHAR2)
  RETURN VARCHAR2 IS
    v_where VARCHAR2 (2000);
  BEGIN
    v_where := 'dept_no = SYS_CONTEXT(''which_depts'', ''dept_no'')';
    RETURN v_where;
  END;
END;


BEGIN

  DBMS_RLS.ADD_POLICY (
    object_schema => 'jgottumu',
    object_name => 'emp',
    policy_name => 'dept_pl',
    policy_function =>'dept_pkg.secure',
    statement_types => 'select');

END;
/

SELECT * FROM EMP;

I am getting error ORA-28113: policy predicate has error when i am trying to run SELECT * FROM EMP;


sumanth_oraAsked:
Who is Participating?
 
slightwv (䄆 Netminder) Commented:
Not sure about your schema but my EMP table's column is DEPTNO not DEPT_NO.

Try making this small change:
    v_where := 'deptno = SYS_CONTEXT(''which_depts'', ''dept_no'')';


I tested your code using 10.2.0.3.

Below is the complete example.
CREATE OR REPLACE PACKAGE scott_dept_context AS
  PROCEDURE set_dept;
END;
/

CREATE OR REPLACE PACKAGE BODY scott_dept_context AS
  PROCEDURE set_dept IS
  BEGIN
    DBMS_SESSION.SET_CONTEXT(
             'which_depts', 
             'dept_no', 
             20);
  END set_dept;
END;
/

drop CONTEXT which_depts;
CREATE CONTEXT which_depts USING scott_dept_context;
       
CREATE OR REPLACE PACKAGE dept_pkg AS
  FUNCTION secure(
    p_owner VARCHAR2, 
    p_object VARCHAR2)
    RETURN VARCHAR2;
END;        
/

CREATE OR REPLACE PACKAGE BODY dept_pkg AS
  FUNCTION secure(
    p_owner VARCHAR2, 
    p_object VARCHAR2)
  RETURN VARCHAR2 IS
    v_where VARCHAR2 (2000);
  BEGIN
    v_where := 'deptno = SYS_CONTEXT(''which_depts'', ''dept_no'')';
	

    RETURN v_where;
  END;
END;
/


BEGIN
  DBMS_RLS.DROP_POLICY (
    object_schema => 'SCOTT',
    object_name => 'emp',
    policy_name => 'dept_pl'
    );

  DBMS_RLS.ADD_POLICY (
    object_schema => 'SCOTT',
    object_name => 'emp',
    policy_name => 'dept_pl',
    policy_function =>'dept_pkg.secure',
    statement_types => 'select');

END;
/

--no rows becuase context isn't set
SELECT * FROM EMP;

exec scott_dept_context.set_dept;
SELECT * FROM EMP;

Open in new window

0
 
yuchingCommented:
in function dept_pkg.secure, try to put a space before dept_no

  v_where := ' dept_no = SYS_CONTEXT(''which_depts'', ''dept_no'')';
0
 
yuchingCommented:
sorry kindly ignore my comments above, when u login in the db, try select SYS_CONTEXT(''which_depts'', ''dept_no'') from dual, is any error returned?
0
 
devindCommented:
replace exiting v_where with following

 v_where := 'SYS_CONTEXT('||q'['which_depts']'||','||q'['dept_no']'||') = '||q'[dept_no]';

refer: http://forums.oracle.com/forums/thread.jspa?threadID=1117535&tstart=0
0
Question has a verified solution.

Are you are experiencing a similar issue? Get a personalized answer when you ask a related question.

Have a better answer? Share it in a comment.

All Courses

From novice to tech pro — start learning today.