I decided to use a PL/SQL function body (returning boolean)
where upper(username) = upper(:P101_USERNAME)
and acl_clearing_level > 0
and rownum = 1
if( l_cnt >= 1 )
This works successfully.
1 person found this helpful
I'm attempting to create a very simple vallidation check on an APEX (5.0) login page.
I want the validation check to make sure the username (:P101_USERNAME) exists in the "acl_users" table and additionally, to ensure they have an access level more than 0.
I've tried using a "Rows returned" validation type and the sql query I'm using is:
from acl_users aclu
where upper(aclu.username) = :P101_USERNAME
and aclu.acl_clearing_level > 0
and rownum = 1;
Testing this outside of APEX returns rows as expected (using a valid username). However, the application seems to be letting me login, regardless of the value of "acl_clearing_level".
Conversely, if I set the type to "No rows returned", every login request is blocked.
Any advice greatly appreciated.
This is due to a logical error in your code.
select count(*) ...without a
group bywill always return one row (which may contain the value "0").
Do not perform tests for existence using
select count(*) from ... where .... Not only is this prone to this type of logic issue, but depending on the predicates used and the absence of indexes/partitions on the table(s) used it may also be very inefficient.
All that the APEX built-in Rows returned condition requires is a simple query containing the required predicates:
select null from acl_users aclu where upper(aclu.username) = :P101_USERNAME and aclu.acl_clearing_level > 0
To perform existence checks in your own code, use this pattern:
... select count(*) n into n from dual where exists (select null from acl_users aclu where upper(aclu.username) = :P101_USERNAME and aclu.acl_clearing_level > 0); if n > 0 ...
This will check that the predicates are satisfied as efficiently as possible and always return one row containing the value 0 or 1, hence it is both performance and unambiguous.