title: Oracle Apex Custom Authentication Error - 'MY_AUTHENTICATION' IS NOT A PROCEDURE
body: <p>Excuse my naivety but I've been fighting this issue for a few hours now with no progress and I'm lost as to what I'm missing. I'm quite new to Oracle and still getting to grips with a lot of the custom functionality.</p>
  
  <p>I'm trying to write a function (following the guidelines on the APEX interface) regarding validating login credentials using a custom authentication scheme for my application.</p>
  
  <p>The guidelines state:</p>
  
  <blockquote>
    <p>Specify the name of the function that will verify the user's username and password, after they were entered on a login page. If you enter nothing, you allow any username/password to succeed. The function itself can be defined in the authentication's 'PL/SQL Code' textarea, within a package or as a stored function.
    This function must return a boolean to the login procedure that calls it. It has 2 input parameters 'p_username' and 'p_password' that can be used to access the values an end user entered on the login page.
    Examples
    Enter the following code in the 'PL/SQL Code' textarea</p>
  </blockquote>
  
  <pre><code>function my_authentication (
      p_username in varchar2,
      p_password in varchar2 )
      return boolean
  is
      l_user my_users.user_name%type := upper(p_username);
      l_pwd  my_users.password%type;
      l_id   my_users.id%type;
  begin
      select id  , password
        into l_id, l_pwd
        from my_users
       where user_name = l_user;
  
      return l_pwd = rawtohex(sys.dbms_crypto.hash (
                         sys.utl_raw.cast_to_raw(p_password||l_id||l_user),
                         sys.dbms_crypto.hash_sh512 ));
  exception
      when NO_DATA_FOUND then return false;
  end;
  </code></pre>
  
  <p>and <code>my_authentication</code> as <code>Authentication Function</code>.</p>
  
  <p>The function I have wrote is not even as complex as I'm not encrypting pwd etc at present to try and just get the thing working</p>
  
  <pre><code>function my_authentication (p_username in varchar2,p_password in varchar2 )
      return boolean
  is
      valid NUMBER;
      returnvalid BOOLEAN;
  begin
      begin
          select 1
          into valid
          from users u
          where u.sys_user.login = lower(p_username) AND u.sys_user.password = p_password;    
      exception
          when NO_DATA_FOUND then valid := 0;
      end;
      returnvalid := valid=1;
      RETURN returnvalid;
  end;
  </code></pre>
  
  <p>To clarify the users table is made up of 3 fields and the one storing the data I want to validate is <code>sys_user</code> (it is an object type with 5 fields: <code>id</code>, <code>fname</code>, <code>lname</code>, <code>login</code>, <code>password</code>).</p>
  
  <p>When I set the Authentication Function Name to <code>my_authentication</code> and attempt to login I get the following error:</p>
  
  <pre><code>ORA-06550: line 1, column 7: PLS-00221: 'MY_AUTHENTICATION' is not a procedure or is undefined 
  ORA-06550: line 1, column 7: PL/SQL: Statement ignored
  </code></pre>
  
  <p>I'm assuming this is coming from where I have changed </p>
  
  <pre><code>apex_authentication.login(    
      p_username =&gt; :P101_USERNAME,
      p_password =&gt; :P101_PASSWORD
  );
  </code></pre>
  
  <p>to </p>
  
  <pre><code>my_authentication(
      p_username =&gt; :P101_USERNAME,
      p_password =&gt; :P101_PASSWORD
  );
  </code></pre>
  
  <p>under the <code>Processing</code>-><code>Process</code>-><code>Login</code>-><code>Source</code> of the Apex interface. What I can't seem to find a solution too is why it is requesting a procedure when the custom Authentication Scheme configuration page clearly asks for a function returning a boolean which is what I have created. I know it states that a login procedure will call it but I have assumed this is the APEX interface initiating a validation procedure where by it looks for my method?</p>
  
  <p>Any help is very much appreciated and I hope I've enough detail to make sense of my issue</p>
  
  <p>Many Thanks</p>