title: Oracle - Function not working
body: <p>Oracle - Function not working</p>
  
  <p>So I got no idea what i'm doing wrong. I've been at it for hours and i'd really appreciate some help.</p>
  
  <p>So basically I have 2 tables, one is called <code>student</code> and it's a list of students with <code>student_no</code> being the primary key, and the other table called <code>enrol</code> which basically has the list of programs that the student is enrolled into.</p>
  
  <p>So i've written a function that compares the logged in student's username, which in this case is the <code>student_no</code>, to the list of students and makes sure that the logged in user is a student. Then it compares the <code>student_no</code> with the <code>enrol</code> table to seek out any programs the user is enrolled into. So that in essence when I <code>SELECT * FROM yaser.enrol</code> (the table owner is yaser) I should see only a few records from the enrol list. Instead I get the error: <code>failed to execute policy function</code>.</p>
  
  <p>Here is my Function:</p>
  
  <pre><code>-- Create policy function to be called when 'ENROL' table is accessed
  create or replace function f_policy_enrol (schema in varchar2, tab in varchar2)
  -- Function will return a string that is used as a WHERE clause                       
  return varchar2
  as
   v_student_no     varchar2(10);
   is_student       number:=0;
   v_user           varchar2(100);
   out_string       varchar2(400) default '1=2 ';
  
  begin
    -- get session user
    v_user := lower(sys_context('userenv','session_user'));
  
    -- Is the user a student?
    begin
      select student_no into v_student_no from student where lower(student_no) = v_user;
      is_student:=1;
    exception
      when no_data_found then
      v_student_no := 0;
    end;
  
    -- If it's a student, then they are only allowed to see their record only.
    if is_student = 1 then
       out_string := out_string||'or student_no = '||v_student_no||' ';
    end if;
  
    return out_string;
  end;
  /
  </code></pre>
  
  <p>And this is the Policy that I invoke:</p>
  
  <pre><code>begin
  dbms_rls.add_policy('yaser',
                      'enrol',
                      'accesscontrol_enrol',
                      'yaser',
                      'f_policy_enrol',
                      policy_type =&gt; dbms_rls.context_sensitive);
  end;
  /
  </code></pre>
  
  <p>As said before..I'm not sure where i'm going wrong. Whether in the policy or function. ANY HELP would be greatly appreciated! If you have any questions, i'm happy to answer just as soon as I get some free time at work!</p>
  
  <p>Thanks in advance!</p>
  
  <p>Yaser</p>