Oracle APEX - ORA-44004: invalid qualified SQL name

I was creating a custom function (for demo purposes) and was stumbled with this error.

ORA-44004: invalid qualified SQL name

I was referring to my code in APEX 4.0 which was calling the procedure as return f_validate; I was trying to do the same in APEX 4.2.1 also. But I was continuously bugged by this or some other error.


Finally when I removed the return and semicolon from Authentication Scheme and just mentioned the function name and it fixed the error. Crazy.

Folow this steps to fix this error : Application Builder / Application ID / Shared Components / Authentication Schemes / Create / Edit.

In Authentication Function Name column just mention the function name : f_validate

A sample piece of validation function is as follows:

CREATE OR REPLACE FUNCTION f_validate(p_username IN VARCHAR2,
                                      p_password IN VARCHAR2)
  RETURN BOOLEAN IS
  lv_cnt NUMBER;
BEGIN
  SELECT COUNT(1)
  INTO   lv_cnt
  FROM   my_users_table
  WHERE  lower(p_username) = lower(email)
  AND    p_password = pass;
  IF lv_cnt = 0 THEN
    RETURN FALSE;
  ELSE
    RETURN TRUE;
  END IF;
EXCEPTION
  WHEN OTHERS THEN
    RETURN FALSE;
END;​

Remember that password in the example is stored in plain text, which may not be the case in real procedures. Use fields that are encrypted.