!!! Overview
We are not DBA experts but we have often been involved with integration work using [{$pagename}].

We shows some [Example code] to get you started with [{$pagename}]

!! DBMS_LDAP - Accessing LDAP From PL/SQL [1]

The DBMS_LDAP package is a PL/SQL API to enable programatic searches and modifications of data within LDAP directories. In this article I'll demonstrate a simple LDAP search.

! Loading the DBMS_LDAP package
The DBMS_LDAP package is not loaded by default. To install it run the following script as SYS.
{{{
SQL> @$ORACLE_HOME/rdbms/admin/catldap.sql
}}}

!! Connect And Authenticate

The first step in any LDAP interaction is to connect to the LDAP server and authenticate yourself. This is done using the init and simple_bind_s functions.
{{{
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_ldap_user,
                                    passwd => l_ldap_passwd);
}}}

!! [Search LDAP Directory|SearchRequest]
Once connected we can search the directory.
{{{
l_attrs(1) := '*'; -- retrieve all attributes 
l_retval := DBMS_LDAP.search_s(ld       => l_session, 
                               base     => l_ldap_base, 
                               scope    => DBMS_LDAP.SCOPE_SUBTREE,
                               filter   => 'objectclass=*',
                               attrs    => l_attrs,
                               attronly => 0,
                               res      => l_message);
}}}

The starting point for the search (base), depth of the search (scope), the attributes searched for (attrs) and the filter can be modified to build complex searches.

!! Loop Through [Search Responses]
 
An LDAP search returns a [Search Responses] which can be looped through.
__Note:__ We do not check for [SearchResultReference]s within this code.

The code assumes all the [Search Responses] are [SearchResultEntry]s.

{{{
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
    ...
    ...
    l_entry := DBMS_LDAP.next_entry(ld  => l_session,
                                    msg => l_entry);
  END LOOP entry_loop;
END IF;
}}}
!! Loop Through Attributes
For each entry we loop through the associated attributes.

{{{
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
  ...
  ...
  l_attr_name := DBMS_LDAP.next_attribute(ld        => l_session,
                                          ldapentry => l_entry,
                                          ber_elem  => l_ber_element);
END LOOP attibutes_loop;
}}}

!! Loop Through Values
Finally, we retrieve the values associated with the attribute.
{{{
<< values_loop >>
FOR i IN l_vals.FIRST .. l_vals.LAST LOOP
  DBMS_OUTPUT.PUT_LINE('ATTIBUTE_NAME: ' || l_attr_name || ' = ' || SUBSTR(l_vals(i),1,200));
END LOOP values_loop;
}}} 

!! Put It All Together
If we put all these stages together we get the following.
{{{
SET SERVEROUTPUT ON SIZE 1000000
DECLARE
  -- Adjust as necessary.
  l_ldap_host    VARCHAR2(256) := 'server01.tshcomputing.com';
  l_ldap_port    VARCHAR2(256) := '389';
  l_ldap_user    VARCHAR2(256) := 'cn=orcladmin';
  l_ldap_passwd  VARCHAR2(256) := 'password';
  l_ldap_base    VARCHAR2(256) := 'cn=Users,dc=tshcomputing,dc=com';

  l_retval       PLS_INTEGER; 
  l_session      DBMS_LDAP.session;
  l_attrs        DBMS_LDAP.string_collection;
  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;
  
BEGIN
  -- 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_ldap_user,
                                      passwd => l_ldap_passwd);

  -- Get all attributes
  l_attrs(1) := '*'; -- retrieve all attributes 
  l_retval := DBMS_LDAP.search_s(ld       => l_session, 
                                 base     => l_ldap_base, 
                                 scope    => DBMS_LDAP.SCOPE_SUBTREE,
                                 filter   => 'objectclass=*',
                                 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.
      DBMS_OUTPUT.PUT_LINE('---------------------------------------');
      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);
        << values_loop >>
        FOR i IN l_vals.FIRST .. l_vals.LAST LOOP
          DBMS_OUTPUT.PUT_LINE('ATTIBUTE_NAME: ' || l_attr_name || ' = ' || SUBSTR(l_vals(i),1,200));
        END LOOP values_loop;
        l_attr_name := DBMS_LDAP.next_attribute(ld        => l_session,
                                                ldapentry => l_entry,
                                                ber_elem  => l_ber_element);
      END LOOP attibutes_loop;
      l_entry := DBMS_LDAP.next_entry(ld  => l_session,
                                      msg => l_entry);
    END LOOP entry_loop;
  END IF;

  -- Disconnect from the LDAP server.
  l_retval := DBMS_LDAP.unbind_s(ld => l_session);
  DBMS_OUTPUT.PUT_LINE('L_RETVAL: ' || l_retval);
END;
/
}}}


! [Oracle SQL Workshop to Bind with LDAP]
We show how to use [Oracle SQL Workshop to Bind with LDAP]

----
[#1] - Largely gathered from [http://www.oracle-base.com/]

!! More Information
There might be more information for this subject on one of the following:
[{ReferringPagesPlugin before='*' after='\n' }]