**Warning, the following example function does not make use of HASH, passwords are stored as clear text which is a security risk....this is only an example for functionality sake!
So should it have taken me all day today to set up a custom authentication system for my APEX application? I like to think not, but in reality, I wish I had known a couple of things in order to do this.
Point 1: The code for the authentication function goes into the Object Browser/Functions area.......not within the authentication page within Shared Objects.
Point 2: On the Shared Objects, Authentication Scheme page, all that goes in there is a call to the function: return check_login; or whatever your function is called.
Point 3: APEX adds in code to your function. You have to annotate your variables in the wizzard, and then you only insert the code between the BEGIN and END statements in your code. Also, in contrast to the ORACLE HTML DB handbook page 380, you must declare your cursor as a nunber...I had some trouble in translating the book style with the demonstration application into my own needs. The following ended up being my code for authentication:
create or replace function "KEY_LOGIN"
(p_username in VARCHAR2,
p_password in VARCHAR2)
SELECT COUNT(*) INTO result_rows FROM personnel WHERE NATO_ID=p_username AND PASSWORD=p_password;
IF result_rows > 0 THEN
All in all, between the auto program code and the books and the demonstration applications, this turned out to be pretty confusing. I hope this discombublated information helps someone out there.