2 Replies Latest reply: Jan 2, 2014 10:06 AM by mtefft RSS

    Virtual Private Database Error ORA-28113 : policy predicate has error

    user591174-Oracle

      Hi All,

      I try to add a VPD policy to table po_headers_all. It prompts error ORA-28113 when I select the table after apply the policy.  I find out that if the returned string for the policy function contains the policy object name then it prompts error

      Here is the example

      BEGIN

      DBMS_RLS.ADD_POLICY (

        object_schema    => 'po',

        object_name      => 'po_headers_all',

        policy_name      => 'xxpo_poauth',

        function_schema  => 'apps',

        policy_function  => 'xxpo_poauth',

        statement_types  => 'select');

      END;

       

      The policy function is

      CREATE OR REPLACE FUNCTION xxpo_poauth(

        schema_var IN VARCHAR2,

        table_var  IN VARCHAR2

      )

      RETURN VARCHAR2

      IS

             lv_return_val VARCHAR2(400);

             lv_user_name  VARCHAR2(400);

             lv_attr_group VARCHAR2(400) DEFAULT 'Ownership Details';

      BEGIN

                lv_return_val := 'po_header_id  = (select po_header_id from po_headers_all where po_header_id = 428)';

                RETURN lv_return_val;

      END xxpo_poauth;


      Since I try to add the policy to table po_headers_all and the policy function return string contain table name po_headers_all.

      Do any one knows if it is a bug or the restriction of the VPF function? and if there any work around?


      Thanks in advance


      Lawrence