Here is an example of code to retrieve information from Active Directory, it will retrieve 2 attributes -email and employee number- from server. The reason for an alphabet loop is because Active Directory has an 1000 records limit and my original script recieved an error:
ORA-31202: DBMS_LDAP: LDAP client/server error: Sizelimit exceeded
I use this alphabet loop so I split records into 26 parts, which avoid the returned records exceed 1000 records. You don't have to do that if your AD team can increase the limit in their side. There is a concept of "paged" result sets in LDAP, but I don't see mention of that functionality in the DBMS_LDAP documentation.
______________________________________________________________________________
declare
--credentials
l_ldap_host VARCHAR2(256) := 'ldap.myserver.domain';
l_ldap_port VARCHAR2(256) := '1234';
l_ldap_user VARCHAR2(256) := 'username';
l_ldap_passwd VARCHAR2(256) := 'password';
-- AD server has limit for 1000 records,
I use a loop to go through 26 letters to get small group of people
--to do this, I assume all employees'
email address start with a letter, AD query is NOT case sensitive
l_alphabet varchar2(26) := 'ABCDEFGHIJKLMNOPQRSTUVWXYZ';
l_retval PLS_INTEGER;
l_session DBMS_LDAP.session;
l_attrs DBMS_LDAP.string_collection;
l_attr_empno varchar2(100) := 'empno';
l_attr_email varchar2(100) := 'email';
l_message DBMS_LDAP.message;
l_entry DBMS_LDAP.message;
l_attr_name VARCHAR2(256);
l_ber_element DBMS_LDAP.ber_element;
l_vals DBMS_LDAP.string_collection;
l_empnum varchar2(256);
l_email varchar2(256);
begin
/*connect to ldap server*/
dbms_output.put_line('Start loading records from Active Directory server');
DBMS_LDAP.USE_EXCEPTION := TRUE;
l_session := DBMS_LDAP.init(hostname => l_ldap_host,
portnum =>
l_ldap_port);
l_retval := DBMS_LDAP.simple_bind_s(ld => l_session, ---if success, it = 0
dn =>
l_ldap_user,
passwd => l_ldap_passwd);
/*if connect successfully*/
l_attrs(1) := l_attr_empno;
l_attrs(2) := l_attr_email;
FOR alphaindex in 1..26 LOOP
l_retval := DBMS_LDAP.search_s(ld => l_session,
base =>
'OU=MyOrg,DC=ldap,DC=myserver,DC=domain',
scope => DBMS_LDAP.SCOPE_SUBTREE,
filter => '(&(objectclass=user)(objectclass=top)('||l_attr_email||'='||substr(l_alphabet,alphaindex,1)||'*))',
attrs =>
l_attrs,
attronly => 0,
res =>
l_message);
IF DBMS_LDAP.count_entries(ld => l_session, msg => l_message) > 0 THEN
-- Get all the entries
returned by our search.
l_entry := DBMS_LDAP.first_entry(ld => l_session, msg =>
l_message);
--<< entry_loop
>>
WHILE
l_entry IS NOT NULL LOOP
-- Get all the attributes for this entry.
l_empnum:=null;
l_email:=null;
l_attr_name := DBMS_LDAP.first_attribute(ld => l_session,
ldapentry => l_entry,
ber_elem =>
l_ber_element);
--<< attributes_loop >>
WHILE
l_attr_name IS NOT NULL LOOP
-- Get all the values for this attribute.
l_vals := DBMS_LDAP.get_values (ld => l_session,
ldapentry => l_entry,
attr => l_attr_name);
if l_attr_name = l_attr_empno then
l_empnum := l_vals(l_vals.FIRST);
elsif l_attr_name = l_attr_email then
l_email := l_vals(l_vals.FIRST);
end if;
--get next attribute
l_attr_name := DBMS_LDAP.next_attribute(ld => l_session,
ldapentry => l_entry,
ber_elem => l_ber_element);
END LOOP attibutes_loop;
--only the value that is associated to an
employee number will be considerred valid record
if l_empnum is not null then
--process database tasks
end if;
l_entry := DBMS_LDAP.next_entry(ld => l_session,
msg => l_entry);
END LOOP entry_loop;
END IF;
end loop;
commit;
-- Disconnect from the LDAP server.
l_retval := DBMS_LDAP.unbind_s(ld => l_session);
dbms_output.put_line('Complete loading records from Active Directory server');
end;
_______________________________________________________________________________
