Get OID group members

Currently, there is no Portal API to get the list of members from a group, they were deprecated after the integration with OID, you can use the wwsec_oid api, however as it is not a published api, Oracle won't support it, here is a simple procedure that retrieves the list of direct users from a given group.

PROCEDURE LIST_MEMBERS (p_group in varchar2)
IS

ldap_host VARCHAR2(256):= '127.0.0.1' ;
ldap_port PLS_INTEGER:= '389';
ldap_user VARCHAR2(256):= 'cn=orcladmin';
ldap_passwd VARCHAR2(256):= 'oracle123';
retval PLS_INTEGER;
my_session DBMS_LDAP.session;
my_pset_coll DBMS_LDAP_UTL.PROPERTY_SET_COLLECTION;
my_property_names DBMS_LDAP.STRING_COLLECTION;
my_property_values DBMS_LDAP.STRING_COLLECTION;
group_handle DBMS_LDAP_UTL.HANDLE;
group_id VARCHAR2(2000);
group_type PLS_INTEGER := DBMS_LDAP_UTL.TYPE_DN;
my_mod_pset DBMS_LDAP_UTL.MOD_PROPERTY_SET;
my_attrs DBMS_LDAP.STRING_COLLECTION;
l_group_rec portal.wwsec_group$%rowtype;
lv_cn varchar2(200);

BEGIN

l_group_rec := portal.wwsec_api.group_info(p_group_id =>
portal.wwsec_api.group_id(upper(p_group)));

group_id := l_group_rec.dn;

DBMS_LDAP.USE_EXCEPTION := TRUE;
my_session := DBMS_LDAP.init(ldap_host,ldap_port);
my_attrs(1) := 'uniquemember';
retval := DBMS_LDAP_UTL.create_group_handle(group_handle,group_type,group_id);
retval := DBMS_LDAP_UTL.get_group_properties(my_session,group_handle,my_attrs,DBMS_LDAP_UTL.ENTRY_PROPERTIES,my_pset_coll);

IF retval != DBMS_LDAP_UTL.SUCCESS THEN
htp.p('get_group_properties returns : ' || to_char(retval));
END IF;

IF my_pset_coll.count > 0 THEN

FOR i in my_pset_coll.first .. my_pset_coll.last LOOP
retval := DBMS_LDAP_UTL.get_property_values(my_pset_coll(i),my_attrs(1),my_property_values);
if my_property_values.COUNT > 0 then
FOR k in my_property_values.FIRST..my_property_values.LAST LOOP
if instr(upper(my_property_values(k)),'CN=USERS') >0 then
lv_cn:=substr( my_property_values(k), (instr(my_property_values(k),'=')+1));
lv_cn:=substr( lv_cn,0,(instr(lv_cn,',')-1));

htp.p('<br>'||lv_cn);
end if;
END LOOP;
end if;
END LOOP;
END IF;

DBMS_LDAP_UTL.free_handle(group_handle);
retval := DBMS_LDAP.unbind_s(my_session);

EXCEPTION
WHEN OTHERS THEN
htp.p(' Error code : ' || TO_CHAR(SQLCODE));
htp.p(' Error Message : ' || SQLERRM);
htp.p(' Exception encountered .. exiting');
END;