Oracle have created a package called DBMS_LDAP, I have to say when I first found it I thought it was new but it has apparently existed in one form or another since Oracle 9i. It is fairly self explanatory to use, the main (useful) function is SEARCH_S which does the querying. The function takes the following parameters;
|SYS.DBMS_LDAP.SEARCH_S Parameter List|
v_SearchUsername VARCHAR2(40) := '@@';
:Result := '';
v_LDAPSession := DBMS_LDAP.INIT('@LDAP server@', '@port@');
v_FunctionReturnValue := DBMS_LDAP.SIMPLE_BIND_S(v_LDAPSession,
v_LDAPAttributeQueryList(1) := 'mail';
v_FunctionReturnValue := DBMS_LDAP.SEARCH_S(
ld => v_LDAPSession,
base => '@base location@', -- "DC=xx,DC=yy=DC=zz"
scope => DBMS_LDAP.SCOPE_SUBTREE,
filter => 'samaccountname=' || v_SearchUsername,
attrs => v_LDAPAttributeQueryList,
attronly => 0,
res => v_LDAPQueryResults);
v_FunctionReturnValue := DBMS_LDAP.COUNT_ENTRIES(v_LDAPSession,
IF DBMS_LDAP.FIRST_ENTRY(v_LDAPSession, v_LDAPQueryResults) IS NOT NULL THEN
:Result := DBMS_LDAP.GET_VALUES(
v_FunctionReturnValue := DBMS_LDAP.UNBIND_S(v_LDAPSession);
You need to do a little updating in order to make this work for your configuration (entering the server, a active directory user account with permission to do the query, your base location, etc) but on our system this runs in a tiny fraction of a second.
NOTE: This patch of code is only returning the first record returned. You will encounter problems if the user name exists in different domains, but that issue hasn't arisen for us and I guess most companies will probably be ok.
The v_berElement is required by the FIRST_ENTRY call in order to allow you to iterate through the results but as we're just interested in the first record returned it is declared, used, but never referenced again in the code above.
Final comment, if you are running 11g you are likely to get;
"ORA-24247: network access denied by access control list (ACL)"
When you attempt to run the command (unless your user has already been granted LDAP access). You need to update the access control list granting access to the connected user. The solution is readily available on Google (but I might create a post over the next few days as I need to do it myself!).