ERROR creating Custom authentication in oracle apex

Question

I am new to Apex and I am trying to create a custom authentication.

Below is the authenticate function I am using:

FUNCTION authenticate(username_in IN VARCHAR2
                                        ,password_in IN VARCHAR2) RETURN BOOLEAN IS
   l_value       NUMBER;
   l_returnvalue BOOLEAN;
 BEGIN
   BEGIN
     SELECT 1
       INTO l_value
       FROM users
      WHERE 1 = 1
        AND upper(users.username) = upper(username_in)
        AND users.password = password_in;
   EXCEPTION
     WHEN no_data_found
          OR too_many_rows THEN
       l_value := 0;
     WHEN OTHERS THEN
       l_value := 0;
   END;
   l_returnvalue := l_value = 1;
   RETURN l_returnvalue;
 END;

The users table I am using has three columns

"id",
"username",
"password"

Below is the error I am seeing when I try to validate the function in PL/SQL block.

ORA-06550: line 1, column 73: PLS-00103: Encountered the symbol "AUTHENTICATE" when expecting one of the following: := . ( @ % ; The symbol ":=" was substituted for "AUTHENTICATE" to continue. ORA-06550: line 1, column 101: PLS-00103: Encountered the symbol "VARCHAR2" when expecting one of the following: ( The symbol "(" was substituted for "VARCHAR2" to continue. ORA-06550: line 2, column 57: PLS-00103: Encountered the symbol "VARCHAR2" when expecting one of the following: ( ORA-06550: line 24,

Thanks.

Tom Tom · Accepted Answer · 2015-04-28T09:33:54

on apex.oracle.com (apex 5.0) : this certainly looks like a bug. But it does not stop you from saving the scheme. Just create or apply, and it will correctly validate your code block and show any correct and relevant errors. Best to report this on the official forum, or if you have a support identifier through the official support.

Note also that your authentication function is incorrect. Here is some relevant information you can also view by viewing the help on the item:

Specify the name of the function that will verify the user's username and password, after they were entered on a login page. If you enter nothing, you allow any username/password to succeed. The function itself can be defined in the authentication's 'PL/SQL Code' textarea, within a package or as a stored function.

This function must return a boolean to the login procedure that calls it. It has 2 input parameters 'p_username' and 'p_password' that can be used to access the values an end user entered on the login page.

Your input parameters have to be those two mentioned!

ERROR creating Custom authentication in oracle apex

1 Answers