title: Implementing VPD predicate function - ORA-28110: policy function or package has error
body: <p>I am trying to implement a predicate function on a table. The package and predicate function compile with no issues, but when I select on the table, I receive this error:</p>
  
  <pre><code>SELECT * FROM MSGG_GUIDES;
  
  ORA-28110: policy function or package VPD674.MSGG_SECURITY_POLICY has error
  </code></pre>
  
  <p>Here is the function I have defined. The code below is a snippet from the package body that the function is a part of. My end goal is to have this function define a policy for the 4 tables you see in the if statements.  </p>
  
  <pre><code>  function MSGG_SECURITY_POLICY (schema_in varchar2, NAME_IN varchar2)
      return varchar2 
      IS 
      where_stmt varchar2(5000);
      BEGIN
  
        if schema_in='VPD674' and NAME_IN='MSGG_GUIDES' THEN
          where_stmt := 'PERSON_ID = MSGG_SESSION.get_user_id';
  
        elsif schema_in='VPD674' and NAME_IN='MSGG_ORDERS' THEN
          where_stmt := 'ORDERING_PERSON = MSGG_SESSION.get_user_id';
  
        elsif schema_in='VPD674' and NAME_IN='MSGG_SIGHTING_REPORTS' THEN
          where_stmt := 'PERSON_ID = MSGG_SESSION.get_user_id';
  
        elsif schema_in='VPD674' and NAME_IN='MSGG_TRIP_HISTORY' THEN
          where_stmt := 'GUIDE_ID = MSGG_SESSION.get_user_id';
  
        end if;
  
      return (where_stmt);
  </code></pre>
  
  <p>Here is how it is applied to the table in question (the one I'm selecting from).</p>
  
  <pre><code>BEGIN
  DBMS_RLS.ADD_POLICY(
    object_schema =&gt; 'VPD674',
    object_name =&gt; 'MSGG_GUIDES',
    policy_name =&gt; 'MSGG_SECURITY_POLICY1',
    function_schema =&gt; 'VPD674',
    policy_function =&gt; 'MSGG_SECURITY_POLICY',
    statement_types =&gt; 'SELECT');
  
  END;
  /
  </code></pre>
  
  <p>Any ideas as to why the select is erroring out? The function compiles without issue. The "show errors" command on either the package, the function or the policy returns "no errors". I have attempted a hard coded version of the function that does not use the get_user_id function as a troubleshooting step, but I still receive the same error. I also tried creating the function outside of the package and applying it to the policy, but that received the same error. </p>
  
  <p>For full disclosure, below is the entire package specification and body creation script. You will see the hardcoded function version I mentioned (MSGG_SECURITY_POLICY_G). </p>
  
  <pre><code>CREATE OR REPLACE PACKAGE MSGG_SESSION AS 
      PROCEDURE authenticate (current_username varchar2, current_password varchar2);  
      function get_user_id 
    RETURN NUMBER;
    function MSGG_SECURITY_POLICY (schema_in varchar2, NAME_IN varchar2)
    RETURN VARCHAR2;
    function MSGG_SECURITY_POLICY_G (schema_var IN VARCHAR2,  table_var  IN VARCHAR2)
    RETURN VARCHAR2;
  end MSGG_SESSION;
  /
  
  CREATE OR REPLACE PACKAGE BODY MSGG_SESSION AS 
      person_id_var NUMBER;
  
    function get_user_id 
          return NUMBER IS BEGIN
  
              return(person_id_var);
  
          end get_user_id;
  
      PROCEDURE authenticate (current_username varchar2, current_password varchar2) IS BEGIN
  
              SELECT personID 
        INTO person_id_var
              FROM MSGG_USER
              WHERE (current_username=username and current_password=password);
  
        DBMS_OUTPUT.put_line(person_id_var);
  
          END authenticate;   
  
    function MSGG_SECURITY_POLICY (schema_in varchar2, NAME_IN varchar2)
      return varchar2 
      IS 
      where_stmt varchar2(5000);
      BEGIN
  
        if schema_in='VPD674' and NAME_IN='MSGG_GUIDES' THEN
          where_stmt := 'PERSON_ID = MSGG_SESSION.get_user_id';
  
        elsif schema_in='VPD674' and NAME_IN='MSGG_ORDERS' THEN
          where_stmt := 'ORDERING_PERSON = MSGG_SESSION.get_user_id';
  
        elsif schema_in='VPD674' and NAME_IN='MSGG_SIGHTING_REPORTS' THEN
          where_stmt := 'PERSON_ID = MSGG_SESSION.get_user_id';
  
        elsif schema_in='VPD674' and NAME_IN='MSGG_TRIP_HISTORY' THEN
          where_stmt := 'GUIDE_ID = MSGG_SESSION.get_user_id';
  
        end if;
  
      return (where_stmt); 
  
      end MSGG_SECURITY_POLICY;
  
  FUNCTION MSGG_SECURITY_POLICY_G( 
    schema_var IN VARCHAR2,
    table_var  IN VARCHAR2
   )
   RETURN VARCHAR2
   IS
    return_val VARCHAR2 (400);
   BEGIN
    return_val := 'PERSON_ID = ''14''';
    RETURN return_val;
   END MSGG_SECURITY_POLICY_G;
  
  end MSGG_SESSION;
  
  /
  </code></pre>