function user_auth(
      p_username in varchar2,
      p_password in varchar2)
    return boolean
  is
    c_username dbms_id_128 not null:=p_username;
    c_password dbms_id_128 not null:=p_password;
    lv_is_authenticated boolean:=false;
  begin
    -- 1) try ldap
    lv_is_authenticated:=apex_ldap.authenticate(p_username => c_username, 
                                                p_password => c_password,
                                                p_search_base => '...',
                                                p_host  => '...');
    -- 2) if failed try dbaccounts
    if not lv_is_authenticated then 
      lv_is_authenticated:=NEED_HELP_FROM_ASK_TOM.DB_AUTHENTICATE(...);
    end if;
    
    return lv_is_authenticated;
  end user_auth;

I strongly urge you to abandon this idea.

Doubling up on authentication methods brings all sorts of problems & risks, such as:

- You're storing the password in two places; this increases the attack surface and chance of a leak
- Password synchronization - how do you ensure password changes apply to both places at the same time?
- Any custom authentication scheme is highly likely to be less secure than LDAP

If LDAP is down, that sounds like a priority zero, drop everything to get it working again type of situation to me. Building a "just in case" fallback doesn't seem worth it to me.

If you absolutely must go ahead with this (again - please don't!) then the basic process is:

- Create a users table to store the HASH of the password + username + salt
- To authenticate users, repeat the hashing process used above on the supplied credentials
- Query the users table for this account and check if the hashes match
- If they do => success, if they don't => failure

Here's a couple of examples of how to do this for APEX & ORDS:

https://blogs.oracle.com/apex/post/custom-authentication-and-authorization-using-built-in-apex-access-control-a-how-to

https://oracle-base.com/articles/misc/oracle-rest-data-services-ords-custom-authentication-schemes

Note that both MD5 and SHA1 hashes are considered insecure these days; you'll want to use bcrypt to do the hashing. I don't know of a way to do this built-in to the database - you can create a Java stored procedure to do this.

As an alternative you may want to consider adding social authentication schemes (e.g. Google, FB, ...) in APEX instead (assuming this is appropriate of course).

https://docs.oracle.com/en/database/oracle/application-express/21.2/aeadm/editing-social-sign-in.html#GUID-693A8416-F42B-4907-8ED3-F4351CDD06AF