DBMS_LDAP example

In my previous post, I talked about using ldapsearch to find an AD object’s objectGUID. Here I am giving an example to use DBMS_LDAP to search an object and return its objectGUID.

CREATE OR REPLACE FUNCTION get_objectguid (v_cn_name IN VARCHAR2)
RETURN VARCHAR2  AS

	v_objectguid   VARCHAR2(100) := NULL;
	
	v_ldap_host    VARCHAR2(256) := 'joe.local.net';
	v_ldap_port    VARCHAR2(256) := '389';
	v_ldap_user    VARCHAR2(256) := 'CN=joe,OU=25342,DC=joe,DC=local,DC=net';
	v_ldap_passwd  VARCHAR2(256) := 'test1234';
	v_ldap_base    VARCHAR2(256) := 'DC=joe,DC=local,DC=net';
	
	v_result               PLS_INTEGER;
	v_session              DBMS_LDAP.session;
	v_search_attrs         DBMS_LDAP.string_collection;
	v_search_results       DBMS_LDAP.MESSAGE;
	v_entry                DBMS_LDAP.MESSAGE;
	v_distinguished_name   VARCHAR2(256);
	v_values               DBMS_LDAP.BINVAL_COLLECTION;
	v_ber_element          dbms_ldap.ber_element;
	v_attr_name            VARCHAR2(256);
	i PLS_INTEGER;
	j PLS_INTEGER;
	
	type char_array is table of char(2) index by pls_integer;
	v_guid_arrary char_array;
	
BEGIN

	-- Choose to raise exceptions.
    DBMS_LDAP.use_exception := TRUE;

	-- initiating the connection to the LDAP server
    v_session := DBMS_LDAP.init(hostname => v_ldap_host, portnum => v_ldap_port);
	
	-- binding the user
	v_result := DBMS_LDAP.simple_bind_s(ld		=> v_session,
										dn		=> v_ldap_user,
										passwd	=> v_ldap_passwd);
	-- attribute name to search
    v_search_attrs(1) := 'objectGUID';

    v_result := DBMS_LDAP.search_s(	ld			=> v_session,
									base		=> v_ldap_base,
									scope		=> DBMS_LDAP.SCOPE_SUBTREE,
									filter     	=> '&(objectClass=user)(cn='||v_cn_name||'*)',
									attrs      	=> v_search_attrs,
									attronly   	=> 0,
									res        	=> v_search_results);

	-- expecting there will be only one entry matched , one attribute objectGUID only
	IF dbms_ldap.count_entries(ld => v_session, msg => v_search_results) > 0
	THEN
		
		v_entry := DBMS_LDAP.first_entry(ld  => v_session, msg => v_search_results);
		v_attr_name := dbms_ldap.first_attribute(ld   => v_session,
												ldapentry => v_entry,
												ber_elem  => v_ber_element);

		v_values := dbms_ldap.get_values_len (ld      => v_session,
												ldapentry => v_entry,
												attr      => v_attr_name);
												
		IF v_values.COUNT > 0
		THEN
			FOR i IN v_values.FIRST .. v_values.LAST
			LOOP
				
				select rawtohex(v_values(i)) into v_objectguid from dual;
			END LOOP;
		END IF;												
	
		j := 1;
		FOR i IN 1 .. 16
		LOOP
			v_guid_arrary(i) := substr(v_objectguid, j, 2);
			j := j +2;
		END LOOP;
	
		v_objectguid := v_guid_arrary(4)||v_guid_arrary(3)||v_guid_arrary(2)||v_guid_arrary(1)||'-';
		v_objectguid := v_objectguid||v_guid_arrary(6)||v_guid_arrary(5)||'-';
		v_objectguid := v_objectguid||v_guid_arrary(8)||v_guid_arrary(7)||'-';
		v_objectguid := v_objectguid||v_guid_arrary(9)||v_guid_arrary(10)||'-';
		v_objectguid := v_objectguid||v_guid_arrary(11)||v_guid_arrary(12)||v_guid_arrary(13)||v_guid_arrary(14)||v_guid_arrary(15)||v_guid_arrary(16);
	
	
	END IF;
	
	IF v_objectguid IS NULL THEN 
        v_objectguid:='No_objectguid'; 
    END IF; 
	
	IF v_entry IS NOT NULL THEN 
		v_result := DBMS_LDAP.msgfree(v_entry);
	END IF;
	
    v_result := DBMS_LDAP.unbind_s(ld   => v_session);	
	
	RETURN(v_objectguid);
  
	EXCEPTION
		WHEN NO_DATA_FOUND THEN
			v_objectguid:='NoGuid---'; 
		RETURN(v_objectguid); 
		
END get_objectguid;
/

Note you may see this error when working with AD because AD has a default sizelimit of 1000 and if the search search returns more than 1000 entries. It fails with the DBMS_LDAP.search_s function.

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 1481
ORA-06512: at "SYS.DBMS_LDAP", line 234
ORA-06512: at "JOE.GET_OBJECTGUID", line 42

The workaround is to

limist the results returned
- use more specific filter
  - for example removing ‘*’ from the filter, “filter => ‘&(objectClass=user)(cn=’||v_cn_name||’*)’” to “filter => ‘&(objectClass=user)(cn=’||v_cn_name||’)’“
  - or adding one more parameter as the 2nd parameter for the fuction get_objectguid as a on/off option for the wildcard “*”.
- add more conditions into the filter
- lower the search base.
Or use other methods instead of using DBMS_LDAP.