How to have 2 authentication schemes / functions working in an Oracle Apex app

Question

I have an app where I have 2 tables for users, one for the employees, one for the clients, as you can see on the model below

So, I have a package (SECAPP) with a hash_pwd function, and 2 authenticate function.

CREATE OR REPLACE PACKAGE SECAPP AS
-->>--------------------------------------------------------------------
 FUNCTION hash_pwd (
  p_username IN VARCHAR2,
  p_password IN VARCHAR2
 ) RETURN VARCHAR2;
--<<--------------------------------------------------------------------

-->>--------------------------------------------------------------------
 FUNCTION authenticate_clients(
  p_username IN VARCHAR2,
  p_password IN VARCHAR2
 ) RETURN BOOLEAN;
--<<--------------------------------------------------------------------

-->>--------------------------------------------------------------------
 FUNCTION authenticate_employes(
  p_username IN VARCHAR2,
  p_password IN VARCHAR2
 ) RETURN BOOLEAN;
--<<--------------------------------------------------------------------
END SECAPP;
/

CREATE OR REPLACE PACKAGE BODY SECAPP IS

-->>--------------------------------------------------------------------
FUNCTION hash_pwd(p_username IN VARCHAR2, p_password IN VARCHAR2)
    RETURN VARCHAR2
    IS
    l_password VARCHAR2(4000);
    l_salt VARCHAR2(4000) := 'mysalt';
BEGIN
    l_password := utl_raw.cast_to_raw(dbms_obfuscation_toolkit.md5
    (input_string => p_password || SUBSTR(l_salt,10,13) || p_username || SUBSTR(l_salt, 4, 10)));
    RETURN l_password;
END hash_pwd;
--<<--------------------------------------------------------------------

-->>--------------------------------------------------------------------
FUNCTION authenticate_clients (p_username IN VARCHAR2, p_password in VARCHAR2)
RETURN BOOLEAN
as
L_COUNT number(1) := 0;
begin

SELECT COUNT(*) INTO l_count FROM CLIENTS
where upper(email) = upper(p_username)
AND pwd = hash_pwd(p_username, p_password);

RETURN (l_count > 0);
END authenticate_clients;
--<<--------------------------------------------------------------------

-->>--------------------------------------------------------------------
FUNCTION authenticate_employes (p_username IN VARCHAR2, p_password IN VARCHAR2)
RETURN BOOLEAN
AS 
l_count NUMBER(1) := 0;
BEGIN
SELECT COUNT(*) INTO l_count FROM EMPLOYES
WHERE UPPER(email) = UPPER(p_username)
AND pwd = hash_pwd(p_username, p_password);

RETURN (l_count > 0);
END authenticate_employes;
--<<--------------------------------------------------------------------
END SECAPP;
/

I would like to have 2 logins portal

the original login page from Apex for the client, and an additional Employees login page that i could copy based on the original.

I've added the 2 authenticate functions in my authenticate schemes

In the Login Page process, there's this function apex_authentication.login(p_username => :P9999_USERNAME, p_password => :P000_PASSSWORD);

I tried changing it for secapp.authenticate_employees(p_username => ....). But it doesn't seems to work, it only works when I keep the original apex function and making my authenticate_employees the current scheme.

How could I achieve 2 authenticate function ?

NB : I dont want to merge my employees and clients table, since they have different data.

Let me know if you need more informations, as it has been a while since I posted on SO.

Thanks in advance

Thomas

---

UPDATE

Thanks Koen Lostrie for the solution, heres my function that check the PAGE_ID and execute either one code of the two authenticate functions

FUNCTION authenticate_wrapper(p_username IN VARCHAR2, p_password in VARCHAR2)
RETURN BOOLEAN
AS
l_count NUMBER(1) := 0;
l_app_page_id VARCHAR2(5) := NV('APP_PAGE_ID');
l_result BOOLEAN := false;
BEGIN

IF (l_app_page_id = '9998') THEN 
l_result := authenticate_clients(p_username => p_username, p_password => p_password);
END IF;
IF (l_app_page_id = '9999') THEN
l_result := authenticate_employes(p_username => p_username, p_password => p_password);
END IF;
RETURN l_result;
END authenticate_wrapper;

or.. with the code of the 2 functions directly in it :

FUNCTION authenticate_wrapper(p_username IN VARCHAR2, p_password in VARCHAR2)
RETURN BOOLEAN
AS
l_count NUMBER(1) := 0;
l_app_page_id VARCHAR2(5) := NV('APP_PAGE_ID');
BEGIN

IF (l_app_page_id = '9998') THEN 
SELECT COUNT(*) INTO l_count FROM clients
where upper(email) = upper(p_username)
AND pwd = hash_pwd(p_username, p_password);

RETURN (l_count > 0);
END IF;
IF (l_app_page_id = '9999') THEN
SELECT COUNT(*) INTO l_count FROM EMPLOYES
WHERE UPPER(email) = UPPER(p_username)
AND pwd = hash_pwd(p_username, p_password);

RETURN (l_count > 0);
END IF;
END authenticate_wrapper;

Answer 1

UPDATE

Thanks Koen Lostrie for the solution, heres my function that check the PAGE_ID and execute either one code of the two authenticate functions

FUNCTION authenticate_wrapper(p_username IN VARCHAR2, p_password in VARCHAR2)
RETURN BOOLEAN
AS
l_count NUMBER(1) := 0;
l_app_page_id VARCHAR2(5) := NV('APP_PAGE_ID');
l_result BOOLEAN := false;
BEGIN

IF (l_app_page_id = '9998') THEN 
l_result := authenticate_clients(p_username => p_username, p_password => p_password);
END IF;
IF (l_app_page_id = '9999') THEN
l_result := authenticate_employes(p_username => p_username, p_password => p_password);
END IF;
RETURN l_result;
END authenticate_wrapper;

or.. with the code of the 2 functions directly in it :

FUNCTION authenticate_wrapper(p_username IN VARCHAR2, p_password in VARCHAR2)
RETURN BOOLEAN
AS
l_count NUMBER(1) := 0;
l_app_page_id VARCHAR2(5) := NV('APP_PAGE_ID');
BEGIN

IF (l_app_page_id = '9998') THEN 
SELECT COUNT(*) INTO l_count FROM clients
where upper(email) = upper(p_username)
AND pwd = hash_pwd(p_username, p_password);

RETURN (l_count > 0);
END IF;
IF (l_app_page_id = '9999') THEN
SELECT COUNT(*) INTO l_count FROM EMPLOYES
WHERE UPPER(email) = UPPER(p_username)
AND pwd = hash_pwd(p_username, p_password);

RETURN (l_count > 0);
END IF;
END authenticate_wrapper;