ORA-31223: DBMS_LDAP: cannot open more than 63 LDAP server connections

Working with LDAP has made me appreciate the maturity of the Oracle RDBMS. That said, LDAP is pretty popular it seems. To that end my cohort in crime Dave Smith and I (Kevin Meade) have been tasked with many a work request to update LDAP entries related to database data. In integrating our databases and LDAP via the DBMS_LDAP package we came across this error. A quick Internet search revealed lots of people with the same error but no answers. It turns out that the error is exactly what it says it is, but that finding the reason for it is another matter. Here we discuss what we think the error means and the three most likely ways to get it.

DBMS_LDAP is a database package supplied by Oracle which provides basic LDAP functionality. It is an API and is somewhat tedious to work with, but does the job when you get the steps right. The steps are simple enough. We note the names of the LDAP calls that we are intersted in:

1) establish a connection   dbms_ldap.INIT
2) authenticate   dbms_ldap.SIMPLE_BIND_S
3) do some stuff
4) disconnect   dbms_ldap.UNBIND_S

At first the use of unbind_s is confusing. A good API usually mimics Newton's Law of Motion: to every action there is always an equal and opposite reaction. From a programmer's perspective this translates to every call should have an opposite call. Thus every open should have a close. Every get should have an put, etc. Looking at the steps for DBMS_LDAP one would have expected a "negative action" for each "action". So for example we see a simple_binds_s so we expect to find the reverse and we do; we see unbind_s. For the INIT we would have expected to see an "UN_INIT" but we do not. It turns out that the unbind_s does the "UN_INIT". I guess it is really a question of naming. UNBIND_S should have been UN_INIT since it is the authentication step (in our case we used simple_bind_s) which needs no reverse. One does not normally un-authenticate. The descriptions of the calls are correct, but the name UNBIND_S does not match what it does. This "bad name" may create some confusion we think when trying to resolve this error.

It is in fact this step UNBIND_S which is the cause of the error ORA-31223 DBMS_LDAP: cannot open more than 63 LDAP server connections. When caling LDAP via DBMS_LDAP, you will usually create a function that does all of the three steps above. It will be common for you to call this function many times based on rows in a databse. For example, if we want to get some data from LDAP for a list of employees, we might execute a SQL call like this:

select emp.*,ldapdata.*
from
       emp
     , table(cast(my_ldap_package.get_emp_data(emp.userid) as ldap_emp_data) ldapdata
where dname = 'SALES'
and emp.email != ldapdata.email
/

The above query would find employees from the SALES department whose email does not match their LDAP email. Of course we are using an oracle object to return the LDAP data and this object is constructed inside our packaged function. Our point here being this is one way to call a LDAP data getter many times. We could also put this select statement or one like it inside a FOR LOOP in PL/SQL and call the LDAP package inside the loop if we wanted to. We have obscured some other details, most notably that our function internally knows which LDAP host and port to connect to based on the database it is running from because we put that info in the function. Dont' worry about that detail.

The packaged function that is called will do the three steps above we noted (along with an LDAP SEARCH). Thus it will establish a connection to LDAP, authenticate with the LDAP server, and when we have processed all our employee rows, disconnect from LDAP thus freeing our LDAP session. It is the call to DBMS_LDAP.UNBIND_S that frees the LDAP session. This is important to note for consider the full text of the LDAP error message.

Oracle Error :: ORA-31223
DBMS_LDAP: cannot open more than string LDAP server connections

Cause
An attempt was made to open more than the maximum allowed LDAP server connections.

Action
Free unused connections.

The resolution according to this error is to FREE UNUSED CONNECTIONS. Of course it does not say exactly how to do this. The fact is, the UNBIND_S does the free, so the way you get this error is to not make this call. Once you get this error your session is stuck. You will have to close your session and start a new one because you will be unable to open new LDAP connections in your currect session. When you close your session oracle will clean up after you by freeing up your "LOST" sessions.

To re-iterate, the error is caused by a failure to call UNBIND_S. If you fail to do this enough times (in our case 63) you will generate the ORA-31223 error.

There are three ways we have found that will make this happen, only one is obvious.

The most obvious way is if you fail to actually code the call to UNBIND_S. Here is a function that will do the steps we have described. Its purpose is to get all LDAP entries that have my name.

   function get_emps return t_emp is

      t_emp_v t_emp := t_emp();

      retval           PLS_INTEGER;

      ldap_host        VARCHAR2(256);
      ldap_port        VARCHAR2(256);
      ldap_user        VARCHAR2(256);
      ldap_passwd      VARCHAR2(256);
      ldap_base        VARCHAR2(256);

      my_session       DBMS_LDAP.session;
      my_attrs         DBMS_LDAP.string_collection;
      my_message       DBMS_LDAP.message;
      my_entry         DBMS_LDAP.message;
      my_attr_name     VARCHAR2(256);
      my_ber_elmt      DBMS_LDAP.ber_element;
      my_vals          DBMS_LDAP.STRING_COLLECTION ;

      dn varchar2(4000);

    begin
 
  -- Choosing exceptions to be raised by DBMS_LDAP library.
      DBMS_LDAP.USE_EXCEPTION := TRUE;

  -- Customization
      ldap_host   := '<ldaphosturl>';
      ldap_port   := '<ldapport>';
      ldap_user   := '<ladp user dn that can read ldap>';
      ldap_passwd := '<ldap user password>';
      ldap_base   := '<ldap base>';

  -- Open a Session 
      my_session := DBMS_LDAP.init(ldap_host,ldap_port);

  -- bind to the directory
      retval := DBMS_LDAP.simple_bind_s(my_session, ldap_user, ldap_passwd);

  -- Decide on the data I want to see
      my_attrs(1) := 'manager';
      my_attrs(2) := 'uid';
      my_attrs(3) := 'title';
      my_attrs(4) := 'employeeNumber';
...

  -- Search LDAP for the data
      retval := DBMS_LDAP.search_s(my_session, ldap_base, DBMS_LDAP.SCOPE_SUBTREE, 'cn=*meade*kevin*', my_attrs, 0, my_message);

  -- count the number of entries returned
      retval := DBMS_LDAP.count_entries(my_session, my_message);

  -- get the first ldap entry
      my_entry := DBMS_LDAP.first_entry(my_session, my_message);

  -- loop through all entryies returned
      while my_entry IS NOT NULL loop

  -- Get the Distinghished Name for an entry
         dn := DBMS_LDAP.get_dn(my_session, my_entry);

  -- reset work variables
         manager := null;
         xuid := null;
         title := null;
         employeeNumber := null;

  -- Get the attribute name of the entry
         my_attr_name := DBMS_LDAP.first_attribute(my_session,my_entry,my_ber_elmt);

         while my_attr_name IS NOT NULL loop

  -- get the values for this entry
            my_vals := DBMS_LDAP.get_values(my_session,my_entry,my_attr_name);
            if my_vals.COUNT > 0 then
               for i in my_vals.FIRST..my_vals.LAST loop
	          if    my_attr_name = 'manager' then manager := substr(my_vals(i), 1, 4000);
	          elsif my_attr_name = 'uid' then xuid := substr(my_vals(i), 1, 4000);
	          elsif my_attr_name = 'title' then title := substr(my_vals(i), 1, 4000);
	          elsif my_attr_name = 'employeeNumber' then employeeNumber := substr(my_vals(i), 1, 4000);
	          end if;
               end loop;
            end if;

  -- move on to the next attribute
            my_attr_name := DBMS_LDAP.next_attribute(my_session,my_entry,my_ber_elmt);

         end loop;

  -- Push a row to the collection
         t_emp_v.extend;
         t_emp_v(t_emp_v.last) := o_emp(
                                           dn
                                         , manager
                                         , xuid
                                         , title
                                         , employeeNumber
                                       );

  -- move on to the next entry
         my_entry := DBMS_LDAP.next_entry(my_session, my_entry);

      end loop;

  -- unbind from the directory  
      retval := DBMS_LDAP.unbind_s(my_session);

  -- send bad our resultset
      return (t_emp_v);
   end;

Notice the very last thing we do is UNBIND_S. If you failed to code this line, or if you had commented it out by accident, then you would not be doing the UNBIND_S call. If you run your LDAP function enough times, you will exceed the number open LDAP sessions you are allowed and your system would fail with the ORA-31223 error. Thus the first thing you should look for is to see if you have failed to code this line or if the line has been commented out.

The second reason this error can occur is you have coded the line but for some reason you are not actually executing it. This will occur in one of two ways:

1) you are branching around it

2) an error has occurred which causes an exception to be raised and thereby forces your code to go to your exception handler or to exit if you don't have one and thus bypass the rest of your routine. Since this routine has no exception handler, if an oracle error occurred, lets say a numeric or value error, then the routine would exit without finishing and thereby not make the call to UNBIND_S.

Number one above is pretty simple to fix, just deskcheck your code and follow its logic to see if there are situations where you might branch around the call to UNBIND_S, or see if it is commented out.

Number two above is not so obvious. You need to determine if you are generating an error which depending upon how you call the routine may not be so easy. The best way (guaranteed to always produce results) is to call an autonomous transaction that writes a row to a message table. It is old school, but works. We found that we had a singleton select that was returning no rows (aka. SINGLE ROW SUBQUERY RETURNS NO ROWS) and thus generating an error. This error caused our function to exit before finishing (and thus not call the UNBIND_S). Furthermore, we were calling the function as a scalar subselect and thus a failure did not generate an error in the main select (one of the features of scalar subqueries) which is why we had to resort to the automomous transaction to find out where the error was comming from.

The third way to get this error (again by not calling the UNBIND_S) really hosed us for several hours. It is new and involves and new and unusual exception NO_DATA_NEEDED. Yep you got that right, I am not mis-coding the name. We are not talking about NO_DATA_FOUND. There is now an exception called NO_DATA_NEEDED. It can't be found in the documentation yet (23-Jun-2010) and a google shows little, but there is an ASKTOMHOME page for it (of course there is). This exception is one of those obscure items that requires you to be using certain features of the database and doing things a certain way. Naturally these conditions were exactly what Dave and I were doing.

The NO_DATA_NEEDED exception is raised automatically inside a PIPELINED TABLE FUNCTION if you fail to pull all rows off the PIPELINED function before you quit. A PIPELINED function is commonly used to return rows based on an object type. But instead of returning a fully populated object, a PIPELINED function returns rows one at a time (more or less). This is what makes it PIPELINED. When building a collection of rows, you pipe one row at a time out of the function as you finish building it. This lets your data transformation pipeline process the row you just piped out, while your function generates the next row. Hence you are creating something akin to an assembly line. Just like you can have several chairs in various stages of construction in a chair assembly line, so too can you have multiple rows in various stages of completeness when you chain together a series of PIPELINED TABLE FUNCTIONS. It has application in ETL processes for one thing, and it keeps memory requirements real low because there is never a need to materialize a complete object in memory at one time. However, a PIPELINED TABLE FUNCTION (like every other Oracle Advanced Technique) has it own specific quirks. One of them goes like this:

select *
from table(pipelined function)
where rownum <= 10
/

This query will get up to the first 10 rows returned by a PIPELINED function. If your pipelined function wants to return 20 rows, what happens? You never go back to the PIPELINED function to close its data retrieval mechanism. Since you are only getting 10 of your 20 rows, you never do the last row and thus the PIPELINED function cannot do its "cleanup" operations. This might include closing cursors or closing files you may have opened with UTL_FILE, or closing LDAP sessions you opened using DBMS_LDAP. This is what got us. In effect, there is potential for a "LEAK" of some kind ot occur. To deal with this, Oracle has created a new exception NO_DATA_NEEDED. When you fail to consume all rows the PIPELINED function wants to give you, this error will be raised inside your PIPELINED function (don't ask me how it knows). You can then catch this exception and "clean up" as needed.

Consider as we suggested that you will need to clean up whatever it is you opened in your PIPELINED function. You can as we did call outside utilities like DBMS_LDAP. Your logic will typically loop through all rows returned in your PIPELINED function and when done, close your LDAP session. But if you fail to consume all rows, and you do not code the NO_DATA_NEEDED exception, then your PIPELINED function will quite without closing your LDAP session, eg. without calling the UNBIND_S routine. Why? Because the NO_DATA_NEEDED exception will send you to your exception handler. Interestingly NO_DATA_NEEDED although an exception, acts like only half an exception. It will force your code to branch to your exception handler or if you do not have one, exit. But beyond that it does not seem to propogate to the next layer for a NO_DATA_NEEDED exception will never be reported if you don't handle it. It will just be ignored. Tom Ktye explains this a little in his article for which a link is provided below.

This is what happened to us. Once we put the following exception in our function, the error went away because we trapped for the NO_DATA_NEEDED exception and closed the LDAP session when it happened.

exception
   when no_data_needed then
-- unbind from the directory  
      retval := DBMS_LDAP.unbind_s(my_session);

So to recap.

ORA-31223: DBMS_LDAP: cannot open more than 63 LDAP server connections This error means you failed to call DBMS_LDAP.UNBIND_S. You failed to do so many times and each time you left a session open to your LDAP server (I would call this a LEAK). Once you reach some limit (I think the default being 63), you will get this error. Once you have this error you will not be able to call LDAP again successfully. You must close your session so that Oracle can clean up for you and close your erroneously left open LDAP sessions. You need to figure out why you are not calling the UNBIND_S call and fix it. Our suggestion is to start with these possible reasons:

1) you did not code the call
2) you commented out the call
3) you branched around the call
4) your code generates some error and goes to an exception handler or otherwise exits and thus bypasses the call
5) your pipelined table function generated a no_data_needed exception which you did not handle to cleanup with

Here is the link to Tom Kytes page on NO_DATA_NEEDED. http://tkyte.blogspot.com/2010/04/nodataneeded-something-i-learned.html

Good luck, Kevin and Dave.