SQL & PL/SQL

Hi experts,

I'm using database 19c for testing some PL/SQL to connect to an OID 12.2.1.4.0.

I've compiled this procedure (get from Tim Hall https://oracle-base.com/articles/misc/oracle-application-express-apex-ldap-authentication#ldaps-support)::)

CREATE OR REPLACE PROCEDURE gip.test_login(p_username IN VARCHAR2,
                                           p_password IN VARCHAR2) IS
 l_ldap_host VARCHAR2(256) := '10.198.146.37';
 l_ldap_port VARCHAR2(256) := '3060';
 l_dn_prefix VARCHAR2(100);
 l_retval  PLS_INTEGER;
 l_session dbms_ldap.session;
BEGIN
 IF p_username IS NULL OR p_password IS NULL THEN
   raise_application_error(-20000, 'Credentials must be specified.');
 END IF;
 -- Choose to raise exceptions.
 dbms_ldap.use_exception := TRUE;
 -- Connect to the LDAP server.
 l_session := dbms_ldap.init(hostname => l_ldap_host, portnum => l_ldap_port);
 l_retval := dbms_ldap.simple_bind_s(ld => l_session, dn => l_dn_prefix || p_username, passwd => p_password);
 l_retval := dbms_ldap.unbind_s(l_session);
END;

Connecting with admin user it works fine:

SQL> BEGIN
 2    gip.test_login(p_username => 'cn=orcladmin', p_password => 'oracle123');
 3  END;
 4  /
PL/SQL procedure successfully completed

But when using another OID user, it fails. Evidence that the credentials are correct:

SQL>  conn aguedage/oracle123@APEX_GIP
Connected to Oracle Database 19c Enterprise Edition Release 19.0.0.0.0 
Connected as aguedage@APEX_GIP

SQL> BEGIN
 2    gip.test_login(p_username => 'cn=aguedage', p_password => 'oracle123');
 3  END;
 4  /
ORA-31202: DBMS_LDAP: LDAP client/server error: Invalid credentials
ORA-06512: at "SYS.DBMS_SYS_ERROR", line 86
ORA-06512: at "SYS.DBMS_LDAP", line 1489
ORA-06512: at "SYS.DBMS_LDAP", line 79
ORA-06512: at "GIP.TEST_LOGIN", line 24
ORA-06512: at line 2

If I go to OID and check the full cn for the user:

PLSQL works fine:

SQL> BEGIN
 2    gip.test_login(p_username => 'cn=Gemma Agueda,o=rss,cn=Users, dc=zenithmedia,dc=es', p_password => 'oracle123');
 3  END;
 4  /
PL/SQL procedure successfully completed

…but users doesn't introduce their full names into the login page of OID, they use the attribute “uid”:

But using uid in the PLSQL doesn't work either:

SQL> BEGIN
 2    gip.test_login(p_username => 'uid=aguedage', p_password => 'oracle123');
 3  END;
 4  /
ORA-31202: DBMS_LDAP: LDAP client/server error: Invalid credentials
ORA-06512: at "SYS.DBMS_SYS_ERROR", line 86
ORA-06512: at "SYS.DBMS_LDAP", line 1489
ORA-06512: at "SYS.DBMS_LDAP", line 79
ORA-06512: at "GIP.TEST_LOGIN", line 24
ORA-06512: at line 2

What am I doing wrong?

Thanks,
Jose.