Calling LDAP from PL/SQL

It seems to be a more frequent requirement to interact with LDAP directories from PL/SQL. Either populating records or reading them.

Here is a short test script to debug basic LDAP connection problems from Oracle PL/SQL. I have included some of the common error messages with a brief description below to help work out what is going wrong.

DECLARE
    v_retval pls_integer;
    v_session dbms_ldap.session;
BEGIN

    dbms_ldap.use_exception := TRUE;
    BEGIN
        v_session := dbms_ldap.init(
                  machine.example.com', -- LDAP hostname
                  389 ); -- ldap port
    EXCEPTION
    WHEN OTHERS THEN
        dbms_output.put_line(
            Error with dbms_ldap.init' ||
            SQLERRM ||'|'||
            SQLCODE);
    END;
    BEGIN
    -- bind with the dn of the user and password
        v_retval := dbms_ldap.simple_bind_s(
                v_session,

                cn=scott,dc=people,dc=example,dc=com',
                'tiger' ); -- password
        dbms_output.put_line('Connected ' || v_retval);
    EXCEPTION
        WHEN OTHERS THEN
            dbms_output.put_line(

                    'Error with simple_bind_s ' ||
                    'SQLERRM: ' || SQLERRM ||' | '||
                    'SQLCODE: ' || SQLCODE ||' | '||
                    'Return Value:' || v_retval);
    END;
    v_retval := dbms_ldap.unbind_s( v_session );
END;
This script can help discover a number of errors. Here are some of the most common.

ORA-24247: network access denied by access control list (ACL)
The call to dbms_ldap.init failed because the database access control list does not allow access to that destination.

So you can check what ACL exists for the database.
If you have access to the dba views you can use

SELECT host, lower_port, upper_port, acl
FROM dba_network_acls;

Or as the user trying to connect to LDAP run

SELECT host, lower_port, upper_port, privilege, status
FROM user_network_acl_privileges;
You may need to get the DBA to create a new ACL for the access to the LDAP directory. They can do this running as sys.
begin
    dbms_network_acl_admin.assign_acl (
        acl => 'acl.xml', -- the filename of the acl
        host => 'machine.example.com', -- LDAP server.
        lower_port => 389, -- LDAP port
        upper_port => 389); -- same LDAP port
end;
/
commit;
ORA-31204: DBMS_LDAP: PL/SQL - Invalid LDAP Session.
This will occur if you try to access a session that does not exist. From this test script if the dbms_ldap.init failed then there is no session when it tries to do the dbms_ldap.simple_bind_s. This error will occur when v_session does not have a valid value.

ORA-31202: DBMS_LDAP: LDAP client/server error: DSA is unwilling to perform. unauthenticated bind (DN with no password) disallowed
When user is passed but the password is blank and LDAP does not allow unauthenticated bind

ORA-31202: DBMS_LDAP: LDAP client/server error: Invalid credentials
Either the user DN or the password is not correct

ORA-31202: DBMS_LDAP: LDAP client/server error: Invalid DN syntax. invalid DN
The string used for the user DN is not a valid format.

Of course there are lots of other things to do with LDAP but this will give a few hints to get started and connected.