Call now: 252-767-6166  
Oracle Training Oracle Support Development Oracle Apps

 E-mail Us
 Oracle Articles
New Oracle Articles

 Oracle Training
 Oracle Tips

 Oracle Forum
 Class Catalog

 Remote DBA
 Oracle Tuning
 Emergency 911
 RAC Support
 Apps Support
 Oracle Support

 SQL Tuning

 Oracle UNIX
 Oracle Linux
 Remote s
 Remote plans
 Application Server

 Oracle Forms
 Oracle Portal
 App Upgrades
 SQL Server
 Oracle Concepts
 Software Support

 Remote S


 Consulting Staff
 Consulting Prices
 Help Wanted!


 Oracle Posters
 Oracle Books

 Oracle Scripts

Don Burleson Blog 







dbms_ldap Tips

Oracle Database Tips by Donald BurlesonSeptember 13, 2015

A continuous feature throughout Oracle's history has been the Lightweight Directory Access Protocol (LDAP).  LDAP is a powerful tool that is central to Oracle Platform Security.  The dbms_ldap package used to manage LDAP using PL/SQL language can perform many operations in a directory server.  The examples that follow will demonstrate some of the more common tasks.


If the dbms_ldap package cannot be located, execute the command below logged in as a sys user in order to create it.


<  Code  7.14 - dbms_ldap.sql

conn sys@ora11g as sysdba


Connected to Oracle 11g Enterprise Edition Release

conn / as sysdba


--Create dbms_ldap package



This will show how to perform a search that will return all entries within a specific LDAP base directory.


set serveroutput on size 10000

 v_ldap_message     dbms_ldap.message;
 v_ldap_entry       dbms_ldap.message;
 v_returnval             pls_integer;
 v_session          dbms_ldap.session;
 v_str_collection   dbms_ldap.string_collection;
 v_entry_index      pls_integer;
 v_ber_element      dbms_ldap.ber_element;
 v_attr_index       pls_integer;
 v_dn               varchar2(256);
 v_attrib_name      varchar2(256);
 i                  pls_integer;
 v_info             dbms_ldap.string_collection ;
 v_ldap_base        varchar2(256);
 v_ldap_port        varchar2(256);
 v_ldap_host        varchar2(256);
 v_ldap_user        varchar2(256);
 v_ldap_passwd      varchar2(256);


--Here you can set your LDAP Server information
 v_ldap_host  := '' ;
 v_ldap_port  := '389';
 v_ldap_user  := 'cn=orcladmin';
 v_ldap_passwd:= 'welcome';
 v_ldap_base  := 'dc=demo_entries';

 v_returnval         := -1;
 dbms_output.put(a => 'DBMS_LDAP Search Example ');
 dbms_output.put_line(a => 'to directory .. ');
 dbms_output.put_line(a => rpad('LDAP Host ',25,' ') || ': ' || v_ldap_host);
 dbms_output.put_line(a => rpad('LDAP Port ',25,' ') || ': ' || v_ldap_port);

 dbms_ldap.use_exception := TRUE;

First, the init function is used to establish a connection with the LDAP server. The information about this connection is then displayed.


 v_session := dbms_ldap.init(
                 hostname => v_ldap_host,
                 portnum => v_ldap_port);
 --Displaying information about the connection
 dbms_output.put_line(a => rpad('Ldap session ',25,' ')  || ': ' || rawtohex(substr(v_session,1,8)) ||'(returned from init)');

--Now, the simple_bind_s function will achieve the user authentication to LDAP directory server.

 v_returnval := dbms_ldap.simple_bind_s(
                   ld => v_session,
                   dn => v_ldap_user,
                   passwd => v_ldap_passwd);
 dbms_output.put_line(rpad('simple_bind_s Returns ',25,' ') || ': '|| to_char(v_returnval));


Here, the search_s function begins searching in a synchronized way for a value placed in the filter parameters.


 v_str_collection(1) := '*'; -- retrieve all attributes
 v_returnval := dbms_ldap.search_s(
              ld => v_session,
              base =>  v_ldap_base,
              scope =>
              filter => 'sn~=PAULO',
              attrs => v_str_collection,
              attronly =>  0,
              res => v_ldap_message);

 dbms_output.put_line(rpad('Search returns: ',25,' ') || ': ' || to_char(v_returnval));
 dbms_output.put_line (rpad('LDAP message: ',25,' ')  || ': ' || rawtohex(substr(v_ldap_message,1,8))
                                                              ||' "value returned from your search!" ');


Next, the count_entries function is used to count the number of entries in a result set. After this, the first_entry function is used to get the first entry of that set.


 v_returnval := dbms_ldap.count_entries(
                   ld => v_session,
                   msg => v_ldap_message);
 dbms_output.put_line(rpad('Number of Entries ',25,' ') || ': '|| to_char(v_returnval));

 --Use first_entry function to return the first entry in a result set
 v_ldap_entry := dbms_ldap.first_entry(ld => v_session,msg =>  v_ldap_message);
 v_entry_index := 1;

 -- Get attributes of each entry found
 while v_ldap_entry is NOT NULL loop
   -- print the current entry
   v_dn := dbms_ldap.get_dn(
              ld => v_session,
              ldapentry => v_ldap_entry);
   dbms_output.put_line (a => '        dn: ' || v_dn);
   v_attrib_name := dbms_ldap.first_attribute(
                       ld => v_session,
                       ldapentry => v_ldap_entry,
                       ber_elem => v_ber_element);
   v_attr_index := 1;
   while v_attrib_name is NOT NULL loop    


The get_values function is used to get all values associated with a given attribute. After that, the next_attribute function is used to return the next attribute of a given entry in the result set.


     v_info := dbms_ldap.get_values(
                  ld => v_session,
                  ldapentry => v_ldap_entry,
                  attr => v_attrib_name);
     if v_info.count > 0 then
       for i in v_info.first..v_info.last loop
         dbms_output.put_line(a => '           ' || v_attrib_name || ' : ' ||substr(v_info(i),1,200));
       end loop;
     end if;

--Function next_attribute return the next attribute of a given entry in the result set
     v_attrib_name := dbms_ldap.next_attribute(
                        ld => v_session,
                        ldapentry => v_ldap_entry,
                        ber_elem => v_ber_element);
     v_attr_index := v_attr_index+1;
   end loop; 


Functions like ber_free and msgfree are used to free memory allocated to the ber_element structure. This frees up the chain of messages returned by synchronous search functions.


      ber => v_ber_element,
      freebuf =>  0);
   v_ldap_entry := dbms_ldap.next_entry(
                      ld => v_session,
                      msg => v_ldap_entry);
   v_entry_index := v_entry_index+1;
 end loop;

 -- Use msgfree function to free up the chain of messages associated with the message handle returned by synchronous search functions

 v_returnval := dbms_ldap.msgfree(
                   lm => v_ldap_message);


Lastly, the unbind_s function is used to close an active LDAP session and finish this example.


 v_returnval := dbms_ldap.unbind_s(
                   ld => v_session);

 dbms_output.put_line(a => rpad('unbind_res Returns ',25,' ') ||  ': ' ||to_char(v_returnval));

 dbms_output.put_line(a => 'Session Closed. Operation terminated!');

  when others then
   dbms_output.put_line(' Error code    : ' || to_char(sqlcode));
   dbms_output.put_line(' Error Message : ' || sqlerrm);
   dbms_output.put_line(' Exception encountered .. exiting');


Many tasks that the dbms_ldap package uses to manage an LDAP Directory Server were touched upon in this chapter. Other tasks and their examples can be found in the Application Developer's Guide for Oracle Identity Management 11g Release 1 (11.1.1) E10186-01 and Oracle Internet Directory Application Developer's Guide Release 2.1.1  A86082-01.

Inside the DBMS Packages

The DBMS packages form the foundation of Oracle DBA functionality.  Now, Paulo Portugal writes a landmark book Advanced Oracle DBMS Packages:  The Definitive Reference

This is a must-have book complete with a code depot of working examples for all of the major DBMS packages.  Order directly from Rampant and save 30%. 



Burleson is the American Team

Note: This Oracle documentation was created as a support and Oracle training reference for use by our DBA performance tuning consulting professionals.  Feel free to ask questions on our Oracle forum.

Verify experience! Anyone considering using the services of an Oracle support expert should independently investigate their credentials and experience, and not rely on advertisements and self-proclaimed expertise. All legitimate Oracle experts publish their Oracle qualifications.

Errata?  Oracle technology is changing and we strive to update our BC Oracle support information.  If you find an error or have a suggestion for improving our content, we would appreciate your feedback.  Just  e-mail:  

and include the URL for the page.


Burleson Consulting

The Oracle of Database Support

Oracle Performance Tuning

Remote DBA Services


Copyright © 1996 -  2020

All rights reserved by Burleson

Oracle ® is the registered trademark of Oracle Corporation.



Oracle Training at Sea
oracle dba poster

Follow us on Twitter 
Oracle performance tuning software 
Oracle Linux poster