5 Replies Latest reply: Mar 3, 2013 12:15 PM by Solomon Yakobson RSS

    Problem with VPD

    Rajat
      Dear Experts,

      I was going through the features of VPD. I had created on to test but after implementing the function in the db when i am executing the below statement it is giving me a error

      select* from emp;

      Error

      SQL Error: ORA-28112: failed to execute policy function
      28112. 00000 - "failed to execute policy function"
      *Cause:    The policy function has one or more error during execution.
      *Action:   Check the trace file and correct the errors.

      The trace file is showing error like this

      *** 2013-03-03 21:29:40.864
      ----------------------------------------------------------
      Policy function execution error:
      Logon user : SCOTT
      Table/View : SCOTT.EMP
      Policy name : VPD_NO_WARD
      Policy function: SCOTT.NO_WARD
      ORA-06550: line 1, column 15:
      PLS-00306: wrong number or types of arguments in call to 'NO_WARD'
      ORA-06550: line 1, column 7:
      PL/SQL: Statement ignored

      *** 2013-03-03 21:29:40.864
      ----------------------------------------------------------
      Policy function execution error:
      Logon user : SCOTT
      Table/View : SCOTT.EMP
      Policy name : VPD_NO_WARD
      Policy function: SCOTT.NO_WARD
      ORA-06550: line 1, column 7:
      PL/SQL: Statement ignored

      which i am unable to understand.


      Policy Function
      --------------------------
      create or replace
      FUNCTION NO_WARD RETURN VARCHAR2 IS
      v_ret VARCHAR2(200);
      BEGIN
      v_ret := ' EMPNO = 7521 ';
      RETURN v_ret;
      END;

      Addition of the policy in DB
      --------------------------------------
      BEGIN
      DBMS_RLS.ADD_POLICY (
      object_schema => 'SCOTT',
      object_name => 'EMP',
      policy_name => 'VPD_NO_WARD',
      function_schema => 'SCOTT',
      policy_function => 'NO_WARD',
      statement_types => 'SELECT, INSERT, UPDATE, DELETE'
      );
      END;
      /

      Coiuld you please help me.

      Regards
      Rajat