SQL & PL/SQL

Hi,

I am trying to implement RLS within our database and am getting the following error message:

ORA-28112: failed to execute policy function

Looking around the web, these seems to point to un-handled exception in my function, but I cannot seem to find anything untoward.

I have tested the function and can confrim that it is returning the desired predicate where appropriate..

Here is the function..

  -- ************************************************************************************
  FUNCTION Fnc_Rls_Control(Pin_Schema IN VARCHAR2) RETURN VARCHAR2 IS
    -- ------------------------------------------------------------------------------------
    -- Author:  MLLOYD
    -- Purpose: Policy function that supplies a predicate to limit access to tables based
    --          on the site code
    -- Created: 30/12/2010 
    -- ------------------------------------------------------------------------------------
    -- Revision History
    -- Date            Version        Comments
    -- 30/12/2010         1           Created
    -- ************************************************************************************
  
    CURSOR Cur_Rls_Site IS
      SELECT s.Site_Code
      FROM   All_Users u
      INNER  JOIN Rls_Discoverer_User_Role Ur
      ON     Ur.User_Id = u.User_Id
      INNER  JOIN Rls_Discoverer_Roles r
      ON     r.Role_Id = Ur.Role_Id
      INNER  JOIN Rls_Discoverer_Role_Sites Rs
      ON     Rs.Role_Id = r.Role_Id
      INNER  JOIN Commons.t_Site_Codes s
      ON     s.Siteid = Rs.Site_Id
      WHERE  u.Username = USER;
  
    Rec_Rls_Site Cur_Rls_Site%ROWTYPE;
  
    Lcl_Predicate    VARCHAR2(2000);
    Lcl_Return_Value VARCHAR2(2000);
  
  BEGIN
  
    Lcl_Predicate    := NULL;
    Lcl_Return_Value := NULL;
  
    ----------------------------------------------------------
    -- CHECK IF USER IS OWNER OF SCHEMA, IF SO, NO PREDICATE  
    ----------------------------------------------------------
    IF Pin_Schema = USER THEN
      Lcl_Return_Value := NULL;
    ELSE
      ----------------------------------------------------------
      -- OBTAIN SITE CODES AVAILABLE TO USER 
      ----------------------------------------------------------
      OPEN Cur_Rls_Site;
    
      LOOP
        FETCH Cur_Rls_Site
          INTO Rec_Rls_Site;
        EXIT WHEN Cur_Rls_Site%NOTFOUND;
      
        Lcl_Predicate := Lcl_Predicate || q'(')' || Rec_Rls_Site.Site_Code ||
 q'(')' || ',';
      
      END LOOP;
    
      IF Lcl_Predicate IS NULL THEN
        Lcl_Return_Value := NULL;
      ELSE
        ----------------------------------------------------------
        -- REMOVE TRAILING COMMA
        ----------------------------------------------------------
        Lcl_Predicate := Rtrim(Lcl_Predicate,
                               ',');
        ----------------------------------------------------------
        -- BUILD FINAL PREDICATE
        ----------------------------------------------------------
        Lcl_Return_Value := 'SITE_CODE IN (' || Lcl_Predicate || ')';
      
      END IF;
    
      CLOSE Cur_Rls_Site;
    
    END IF;
  
    RETURN Lcl_Return_Value;
  
  EXCEPTION
    WHEN OTHERS THEN
      IF Cur_Rls_Site%ISOPEN THEN
        CLOSE Cur_Rls_Site;
        RETURN Lcl_Return_Value;
      END IF;
    
  END Fnc_Rls_Control;
  -- ************************************************************************************

I have applied this to a specifc table using the following:

-- ADD POLICY
BEGIN
  DBMS_RLS.ADD_POLICY (
    object_schema    => 'REPORTING',
    object_name      => 'URS_OP_VISIT_STATISTICS',
    policy_name      => 'ACCESS_POLICY',
    function_schema  => 'REPORTING',
    policy_function  => 'PKG_REPORTING.FNC_RLS_CONTROL',
    statement_types  => 'SELECT'
   );
 END;

Have I missed anything obvious?

Regards

Mark