APEX

Hi guys, hopefully someone can help me solving this problem! Will really appreciate any help!

So I have a form to create a new employee account, which has these two fields:

1. P1_Username - a text field
2. P1_Message - a display-only field

What I am trying to accomplish is, whenever a user type in a new username and navigate away from the Username field, a dynamic event (lose focus from Username) will be triggered and set value of the Message field depending on whether the username already exist or not in DB. So here is my setup and PL/SQL code:

• Set dynamic action on Username:
  • Event: Lose Focus
  • Selection type: Item
  • Item: P1_Username
• On True:
  • Action: Set Value
  • Set type: PL/SQL function body
  • Affected element: P1_Message

And this is the PL/SQL function:

DECLARE
    is_username_exist number := 0;
BEGIN
  SELECT 
      COUNT(*)
  INTO  
      is_username_exist  
  FROM  
      Account a
  WHERE 
      lower(a.Name) = lower(:P1_Username);  
  
  if nvl(is_username_exist,0) > 0 then  
    RETURN 'Account already exist';
  else
    RETURN '';
  end if;
END;

When I run this, it seems the dynamic action only happens once (on page load) and it will set empty string for the P1_Message. When I typed in an existing username and navigate to next field, the event is not triggered / the Message field still empty.

Any idea what went wrong? Or any other solution to achieve this username validation? Thanks!

I am using Apex v5.0.3