4 Replies Latest reply on Oct 8, 2013 1:33 PM by ReubenC

    How to retrive Active users count using customized LDAP

    Balaji

      Hi,

       

      I need Active users count through my customized function get_attribute_ldap, but it gives me the following error, please help me out how to get the active users count and what is wrong with the below function.

       

      --------------- Customized LDAP function to get the Active users count  ----------------

       

      CREATE OR REPLACE function get_attribute_ldap (p_uid           IN varchar2 := NULL

                           , p_search_filter IN varchar2 := NULL

                           , p_attribute     IN varchar2 )

          return varchar2

      is

        g_session     DBMS_LDAP.session;

        g_ldap_base   varchar2(1000) := 'DC=serv1,DC=bsno,DC=org';

        retval       pls_integer;

        count_found  pls_integer;

        l_search_filter  varchar2(4000);

        l_entry      dbms_ldap.message;

        l_message    dbms_ldap.message;

        l_attrs      dbms_ldap.string_collection;

        l_vals       dbms_ldap.string_collection ;

        l_attr_name  varchar2(4000);

        l_attr_val   varchar2(4000);

        l_ber_elmt   dbms_ldap.ber_element;

        entry_index  pls_integer;

        ldap_invalid_ber_element  exception;

        pragma exception_init(ldap_invalid_ber_element  , -31201);

      begin

        if p_uid is null then

          l_search_filter := p_search_filter;

        else

          l_search_filter := 'name=' || p_uid;

        end if;

          l_attr_val := NULL;

        g_session   := dbms_ldap.init('d2o04.serv1.bsno.org','389');

        l_attrs(1) := p_attribute;

        retval := dbms_ldap.search_s(g_session

                                   , g_ldap_base

                                   , dbms_ldap.SCOPE_SUBTREE

                                  , l_search_filter

                                   , l_attrs

                                   , 0

                                   , l_message);

         count_found := dbms_ldap.count_entries(g_session, l_message);

        l_entry := dbms_ldap.first_entry(g_session, l_message);

        entry_index := 1;

        if l_entry is not null then

        begin

          l_attr_name := dbms_ldap.first_attribute(g_session

                                                 , l_entry

                                                 , l_ber_elmt);

          if l_attr_name IS NOT NULL then

            l_vals := dbms_ldap.get_values (g_session

                                          , l_entry

                                          , l_attr_name);

            if l_vals.count > 0 then

              l_attr_val := substr(l_vals(l_vals.first),1,4000);

            end if;

          end if;

          dbms_ldap.ber_free(l_ber_elmt, 0);

          exception

            when ldap_invalid_ber_element then

               null;

        end;

        end if;

        retval := dbms_ldap.msgfree(l_message);

        retval := dbms_ldap.unbind_s(g_session);

        return l_attr_val;

      end get_attribute_ldap;

       

      ----------------- The following code is executed to get the count  ------------------------

       

      declare

      v_count varchar2(4000);

      begin

      v_count :=get_attribute_ldap(null,'objectclass=*','(objectCategory=person)(objectClass=user)(!userAccountControl:1.2.840.113556.1.4.803:=2)(employeeID=*)(extensionAttribute1=*)(!extensionAttribute1=A)(!extensionAttribute1=S)');

      dbms_output.put_line(v_count);

      end;

       

       

      -------------------  I got the below error  ------------------

       

      ORA-31202: DBMS_LDAP: LDAP client/server error: Sizelimit exceeded

      ORA-06512: at "SYS.DBMS_SYS_ERROR", line 86

      ORA-06512: at "SYS.DBMS_LDAP", line 1489

      ORA-06512: at "SYS.DBMS_LDAP", line 234

      ORA-06512: at "PMO.GET_ATTRIBUTE_LDAP", line 32

      ORA-06512: at line 4

       

       

      Regards,

      Balaji