Test an APEX user and automatically convert plain text passwords into hashed passwords

is_valid_user.sql

create or replace function is_valid_user(
  p_username varchar2, 
  p_password varchar2
)
return boolean
as
  -- constants for different passwords (also very helpful if you change how you hash user passwords in the future)
  C_PLAIN_TEXT constant varchar2(10) := 'VER1';
  C_APEX_HASH  constant varchar2(10) := 'VER2';
  C_SALT       constant varchar2(20) := 'c923kXm1027cnxslaH8S';
  l_test_hash  varchar2(255);

  function get_hash(
    p_username varchar2,  
    p_password varchar2, 
    p_pass_version varchar2)
  return varchar2
  as
    l_hash varchar2(255);
  begin

    if(nvl(p_pass_version,C_PLAIN_TEXT) = C_PLAIN_TEXT) then
      return p_password;
    end if;

    if p_pass_version = C_APEX_HASH then
        return apex_util.get_hash(
            p_values => apex_t_varchar2(p_username,C_SALT, p_password)
          , p_salted => false -- not salted by apex so this is value repeatable in other sessions
        );
    end if;
 
  end get_hash;
begin

  for user in (
    select password as password, pass_version as pass_version
    from stu_user
    where username = p_username
  )
  loop
    l_test_hash := get_hash(p_username, p_password, user.pass_version);
    
    if user.password = l_test_hash then
      -- automatically convert plain text passwords into hashes as the users attempt to login.
      if user.pass_version = C_PLAIN_TEXT then
      
        l_test_hash := apex_util.get_hash(
          p_values => apex_t_varchar2(p_username,C_SALT, p_password)
        , p_salted => false -- not salted by apex so this is value repeatable in other sessions
        );
        
        update stu_user
          set password = l_test_hash,
              pass_version = C_APEX_HASH
         where username = p_username;
      end if;
       
      return true;
    end if;
  end loop;

end is_valid_user;