How to add a new member to an existing OID group using the programmatic interfaces

------- cut here -------
DECLARE
retval PLS_INTEGER;
my_session DBMS_LDAP.session;
group_dn VARCHAR2(256);
group_array DBMS_LDAP.MOD_ARRAY;
group_vals DBMS_LDAP.STRING_COLLECTION ;
ldap_host VARCHAR2(256);
ldap_port VARCHAR2(256);
ldap_user VARCHAR2(256);
ldap_passwd VARCHAR2(256);
group_name VARCHAR2(256);
user_name VARCHAR2(256);
BEGIN
retval := -1;
-- Please customize the following variables as needed
ldap_host := 'mlc2.acme.org';
ldap_port := '3060';
ldap_user := 'cn=orcladmin';
ldap_passwd:= 'welcome1';
group_name := 'cn=mynewgroup,dc=acme,dc=org';
user_name := 'cn=john.doe,cn=users,dc=acme,dc=org';
-- end of customizable settings

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

-- Initialize ldap library and get session handle.
my_session := DBMS_LDAP.init(ldap_host,ldap_port);

DBMS_OUTPUT.PUT_LINE (RPAD('Ldap session ',25,' ') || ': ' ||
RAWTOHEX(SUBSTR(my_session,1,8)) ||
'(returned from init)');

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

DBMS_OUTPUT.PUT_LINE(RPAD('simple_bind_s Returns ',25,' ') || ': '
|| TO_CHAR(retval));

-- DN for Entry to be updated

group_dn := group_name;

DBMS_OUTPUT.PUT_LINE(RPAD('Updating Entry for DN ',25,' ') ||
': [' || group_dn || ']');

-- Create and setup attribute array(group_array) for updated entry
group_array := DBMS_LDAP.create_mod_array(1);

group_vals(1) := user_name;

DBMS_LDAP.populate_mod_array(group_array,DBMS_LDAP.MOD_ADD,
'uniquemember',group_vals);

-- Modify entry in ldap directory
retval := DBMS_LDAP.modify_s(my_session,group_dn,group_array);

DBMS_OUTPUT.PUT_LINE(RPAD('modify_s Returns ',25,' ') || ': ' ||
TO_CHAR(retval));

-- Free attribute array (group_array)
DBMS_LDAP.free_mod_array(group_array);

-- Unbind from ldap directory
retval := DBMS_LDAP.unbind_s(my_session);

DBMS_OUTPUT.PUT_LINE(RPAD('unbind_res Returns ',25,' ') || ': ' ||
TO_CHAR(retval));

DBMS_OUTPUT.PUT_LINE('Directory operation Successful .. exiting');

-- Handle Exceptions
EXCEPTION
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');

END;
/
------- end cut --------

To use the above sample code do the following:
1.	Save the above text indicated between the begin/end cut lines into a file named addtogroup.sql. Note that the filename and case is not important but will be called addtogroup.sql in this note.

2.	Login to sqlplus using any account that has execute privileges on the dbms_ldap package. 

3.	To see the limited debugging output from the SQL script then be sure to issue the following command before executing the addtogroup.sql script. 

set serveroutput on;

This step is not a requirement but if it is not done then the only output from sqlplus will be “PL/SQL procedure successfully completed.” This does not indicate if the group was actually modified or if there was an error. All it indicates is that the addtogroup.sql script completed. Therefore it is highly recommended to use the “set serveroutput on” command.

If the server output is enabled as recommended in step 3 and there are no errors in the addtogroup.sql script then the following output would be expected:

Ldap session : 02000000(returned from init)
simple_bind_s Returns : 0
Updating Entry for DN : [cn=mynewgroup,dc=acme,dc=org]
modify_s Returns : 0
unbind_res Returns : 0
Directory operation Successful .. exiting

PL/SQL procedure successfully completed.

The user will be added to the group. This can be confirmed by using ldapsearch or through Oracle Directory Manager (ODM).

If any errors are encountered (such as invalid user/pass, duplicate entry, etc... then a PL/SQL exception will occur and an error message will be displayed. For example, if the user was already a part of the group then the following error will be displayed:

Ldap session : 02000000(returned from init)
simple_bind_s Returns : 0
Updating Entry for DN : [cn=mynewgroup,dc=acme,dc=org]
Error code : -31202
Error Message : ORA-31202: DBMS_LDAP: LDAP client/server error: Type or value
exists. uniquemember attribute has duplicate value.
Exception encountered .. exiting

PL/SQL procedure successfully completed.