Can't retrieve data from a function during login Oracle Apex

I'm trying to retrieve the "RESPONSABLE" field from my EMPLOYES table during the login.

I created a function called "employe_resp" which SELECT the responsable column into a variable using the username and hashed password in the WHERE condition.

Here's the EMPLOYES table

Here's my function

FUNCTION employe_resp (p_username IN VARCHAR2, p_password IN VARCHAR2)
RETURN VARCHAR2
IS
  v_resp EMPLOYES.responsable%TYPE;
BEGIN
  SELECT responsable
  INTO v_resp
  FROM EMPLOYES
  WHERE UPPER(email) = UPPER(p_username)
  AND pwd = hash_pwd(p_username, p_password);

  RETURN v_resp;
END employe_resp;

my application item is called G_RESP

On my login page, I created this process :

When logging in, I have a "data not found" error. The problem seems to come from my SELECT statement inside my function, but I don't understand why, because when I execute the SELECT using raw datas in SQL Commands, it's working

Does anyone have a tip or a workaround ?

Thanks in advance

Thomas

This is query you used in that function:

SELECT responsable
  INTO v_resp
  FROM EMPLOYES
  WHERE UPPER(email) = UPPER(p_username)
  AND pwd = hash_pwd(p_username, p_password);

What does hash_pwd expect? You passed p_username and p_password as they are, but line above suggests that p_username might need to be put into UPPERCASE. (What about p_password?)

Therefore: are you sure that hash_pwd returned correct value? Because, the next select you posted:

select responsable from employees 
where email = 'ptutton2@unesco.org'
  and pwd = 'AC9F...5F'

isn't exactly what you use in function as you PRESUMED that password string is in uppercase and email in lowercase.

I think there is something wrong with the logic here. There are 2 concepts and you're mixing them.

Concept 1:Authentication: this is about the user logging into the application

Concept 2:Authorization: this is about what the user can do in an application

Note that you can setup your authorization as such that a user does not have access to any functionality in the application.

The password is only needed in the authentication function. Do not mix other (authorization) functionality in the authentication process. Instead create application processes (or computations) to populate the application item G_RESP. To run those queries, no password check is needed.

Or even better, create appropriate authorization schemes for each user role/responsibility. No password checks are needed for those queries either.

UnCOMMITted data is only visible within the session that created it (and will ROLLBACK at the end of the session if it has not been COMMITted). If you can't see the data from another session (i.e. in Oracle Apex) then make sure you have issued a COMMIT command in the client where you INSERTed the data.

Note: even if you connect as the same user, this will create a separate session and you will not be able to see the uncommitted data in the other session.

From the COMMIT documentation:

> Until you commit a transaction:
>
> - You can see any changes you have made during the transaction by querying the modified tables, but other users cannot see the changes. After you commit the transaction, the changes are visible to other users' statements that execute after the commit.
> - You can roll back (undo) any changes made during the transaction with the ROLLBACK statement (see ROLLBACK).