There is a bug in the latest version Oracle 23c FREE db (23.3.0.23.09) causing any query from the APEX_APPL_ACL_USERS view to fail with ORA-00924: missing BY keyword. This means that the Application Builder search doesn't work and the Application Access Control functionality doesn't work.
SQL> select id,
2 user_name,
3 role_names
4* from apex_appl_acl_users;
Error starting at line : 1 in command -
select id,
user_name,
role_names
from apex_appl_acl_users
Error at Command Line : 4 Column : 6
Error report -
SQL Error: ORA-00924: missing BY keyword
00924. 00000 - "missing BY keyword"
*Cause: The keyword BY was omitted in a GROUP BY, ORDER BY, or
CONNECT BY clause. In a GRANT statement, the keyword
IDENTIFIED must also be followed by the keyword BY.
*Action: Correct the syntax. Insert the keyword BY where
required and then retry the statement.
More Details :
https://docs.oracle.com/error-help/db/ora-00924/