CREATE OR REPLACE PROCEDURE GET_OID_GROUP_MEMBERSHIP (p_entry IN VARCHAR2) IS
/******************************************************************************
   NAME:       GET_OID_GROUP_MEMBERSHIP
   PURPOSE:
        Pour un user, affiche tous les groupes dont il est membre,
       en récursif ou non
     Pour un groupe : idem + liste ses propres membres.
******************************************************************************/
 retval              PLS_INTEGER;
 my_session          DBMS_LDAP.session;
  
 subscriber_handle   DBMS_LDAP_UTL.HANDLE;
 sub_type            PLS_INTEGER := DBMS_LDAP_UTL.TYPE_DN;
 subscriber_id       VARCHAR2(2000) := 'dc=groupe-atlantic,dc=com';
 my_attrs            DBMS_LDAP.STRING_COLLECTION;
 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;
 user_handle         DBMS_LDAP_UTL.HANDLE;
 user_id             VARCHAR2(2000);
 user_type           PLS_INTEGER := DBMS_LDAP_UTL.TYPE_DN;
 lb_is_group         boolean := false;
 lb_is_user          boolean := false;
 
 lc_temp             VARCHAR2(2000);
   
BEGIN  

 retval := connect_to_oid(my_session);
 If retval <> DBMS_LDAP.SUCCESS Then
     htp.p('Erreur de connexion a la base (connect_oid)');
  return; 
 End If;  
 
 -- Create Subscriber Handle
 retval := DBMS_LDAP_UTL.create_subscriber_handle(subscriber_handle, sub_type, subscriber_id);
 If retval != DBMS_LDAP_UTL.SUCCESS  Then
    Htp.p('create_subscriber_handle returns : ' || to_char(retval));
 End If;
 -- Verifie la validité de l'entrée saisie
 If existe_oid_user(p_entry)  = DBMS_LDAP.SUCCESS Then lb_is_user  := True; End If;
 If existe_oid_group(p_entry) = DBMS_LDAP.SUCCESS Then lb_is_group := True; End If;
 
 If not lb_is_user and not lb_is_group Then
     Htp.p('Utilisateur ou Groupe '||p_entry||' inconnu');
     return; 
 End If;
 
 If lb_is_group Then
   
  group_id := 'cn='||p_entry||','||common.ldap_base_groups;
  user_id  := group_id; -- on s'en sert comme un user pour la recherche get_group_membership
  
  -- Create Group Handle
   retval := DBMS_LDAP_UTL.create_group_handle(group_handle,group_type,group_id);
  If retval != DBMS_LDAP_UTL.SUCCESS  Then
      Htp.p('create_group_handle returns : ' || to_char(retval)); 
  End If;
   -- Set Group handle properties (link subscriber to group )
   retval := DBMS_LDAP_UTL.set_group_handle_properties(group_handle, DBMS_LDAP_UTL.SUBSCRIBER_HANDLE, subscriber_handle);
  If retval != DBMS_LDAP_UTL.SUCCESS  Then
      Htp.p('set_group_handle_properties returns : ' || to_char(retval));
  End If;
  
 Else
   user_id := 'cn='||p_entry||',cn=users,dc=groupe-atlantic,dc=com';
 End If;
  
 -- Create User Handle
 retval := DBMS_LDAP_UTL.create_user_handle(user_handle,user_type,user_id);
 If retval != DBMS_LDAP_UTL.SUCCESS  Then
     Htp.p('create_user_handle returns : ' || to_char(retval));
 End If;
 -- Set User handle properties
 retval := DBMS_LDAP_UTL.set_user_handle_properties(user_handle, DBMS_LDAP_UTL.SUBSCRIBER_HANDLE, subscriber_handle);
 If retval != DBMS_LDAP_UTL.SUCCESS  Then
     Htp.p('set_user_handle_properties returns : ' || to_char(retval));
 End If;

 
 -- Debut d'écriture en sortie 
 Htp.p('<font face=Verdana size="1"><table align=center border=1 style="font-family:Verdana; font-size:12px">');
 Htp.p('<tr align=center style="font-weight:bold; background-color:#006699; color:#FFFFFF">');
 If lb_is_group Then
  Htp.p('<td>Membres</td><td>&nbsp;Groupe cherch&eacute;&nbsp;</td><td>Est membre de<br>(1er niveau)</td><td>Est membre de<br>(tous niveaux)</td>');
 Else
  Htp.p('<td>&nbsp;Utilisateur&nbsp;</td><td>Est membre de<br>(1er niveau)</td><td>Est membre de<br>(tous niveaux)</td>'); 
 End If; 
 Htp.p('</tr><tr valign="middle">');
 
 
 -- 1ere colonne : affiche les membres pour un groupe
 If lb_is_group Then
 
   Htp.p('<td>');
   
   my_attrs (1) := 'uniquemember';
   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(''); --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
                  lc_temp := SUBSTR (my_property_values (k), (INSTR (my_property_values (k), '=') + 1 ) );
                  lc_temp := SUBSTR (lc_temp, 0, (INSTR (lc_temp, ',') - 1));
      If lc_temp <> 'portal' Then Htp.p(lc_temp); Htp.br; End If;
               --End If;
            End Loop;
         End If;
      End Loop;
   End If;
   
   Htp.p('</td>');
  
 End If;
 
 
 -- 2ème colonne : affiche l'entrée saisie
 Htp.p('<td align=center><strong>'||p_entry||'</strong></td>'); 
 
 -- 3ème colonne : affiche le membership sur 1 niveau
 Htp.p('<td>');
   
 -- Get Group Membership SUR 1 NIVEAU
 my_attrs.delete();
 my_attrs(1) := 'cn';
 retval := DBMS_LDAP_UTL.get_group_membership ( my_session,
                                user_handle,
                                DBMS_LDAP_UTL.DIRECT_MEMBERSHIP,
                                my_attrs,
                                my_pset_coll );
 If retval != DBMS_LDAP_UTL.SUCCESS  Then
      Htp.p(''); --Htp.p('get_group_membership 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 my_property_values(k) <> 'AUThenTICATED_USERS' and my_property_values(k) <> 'OracleDASCreateGroup' Then  
         Htp.p(my_property_values(k) || '<br>');
   End If;
        End Loop;
  else
        Htp.p('my_property_values vide');   
     End If;
   End Loop;
 Else
     Htp.p('my_pset_coll vide');
 End If;
 
 
 -- 4ème colonne : affiche le membership sur tous les niveaux (récursif)
 Htp.p('<td>');
   
 -- Get Group Membership SUR 1 NIVEAU
 my_attrs.delete();
 my_attrs(1) := 'cn';
 retval := DBMS_LDAP_UTL.get_group_membership ( my_session,
                                user_handle,
                                DBMS_LDAP_UTL.NESTED_MEMBERSHIP,
                                my_attrs,
                                my_pset_coll );
 If retval != DBMS_LDAP_UTL.SUCCESS  Then
      Htp.p(''); --    Htp.p('get_group_membership 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 my_property_values(k) <> 'AUThenTICATED_USERS' and my_property_values(k) <> 'OracleDASCreateGroup' Then  
         Htp.p(my_property_values(k) || '<br>');
   End If;
        End Loop;
  else
        Htp.p('my_property_values vide');   
     End If;
   End Loop;
 Else
     Htp.p('my_pset_coll vide');
 End If;

 Htp.p('</td></tr></table></font>');
 -- Free handle
 DBMS_LDAP_UTL.free_handle(subscriber_handle);
 DBMS_LDAP_UTL.free_handle(user_handle);
 DBMS_LDAP_UTL.free_handle(group_handle);
  -- unbind from the directory  
 retval := disconnect_from_oid(my_session);
 
 
-- Handle Exceptions
 EXCEPTION
  WHEN OTHERS Then
   Htp.p(' Error code    : ' || TO_CHAR(SQLCODE));
   Htp.p(' Error Message : ' || SQLERRM);
   Htp.p(' Exception encountered .. exiting');
  
  
End GET_OID_GROUP_MEMBERSHIP;
/