DBMS_LDAP - Accessing LDAP From PL/SQL

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:

Create DBMS_LDAP
Connect And Authenticate
Search Directory
Loop Through Entries
Loop Through Attributes
Loop Through Values
Put It All Together

Create DBMS_LDAP

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 Directory

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 Entries

The search returns a list of entries which can be looped through using:

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 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:

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;
/

For more information see:

The DBMS_LDAP PL/SQL Package

Hope this helps. Regards Tim...

Back to the Top.