5 Replies Latest reply: Jul 19, 2012 10:35 PM by 783719 RSS

    DBMS_LDAP.add_s always go into infinite loop

    53473
      Here's my problem.

      I copied/pasted and modified the program from:
      Note 281807.1 How To Manually Create Portal Users via PL/SQL (9.0.4.x) 31-AUG-2004

      The following is my program:

      CREATE OR REPLACE PROCEDURE addusers (p_username IN VARCHAR2
      ,p_password IN VARCHAR2
      ,p_email IN VARCHAR2) AS
      err VARCHAR2(100);
      num NUMBER;
      filedata VARCHAR2(50);
      Retval Pls_Integer;
      Entry_index Pls_Integer;
      My_Session Dbms_Ldap.Session;
      Insert_String VarChar2(3000);
      My_DN VarChar2(256);
      My_CN VarChar2(200);
      My_Array DBMS_LDAP.MOD_ARRAY;
      My_ModArray DBMS_LDAP.MOD_ARRAY;
      My_Vals DBMS_LDAP.STRING_COLLECTION;
      My_Vals_Int DBMS_LDAP.STRING_COLLECTION;
      My_Modval DBMS_LDAP.STRING_COLLECTION;
      Ldap_Host VarChar2(256):='xxxxxxxx.yyyyyy.zzz';
      Ldap_Port VarChar2(256):='389';
      Ldap_User VarChar2(256):='cn=orcladmin';
      Ldap_Passwd VarChar2(256):='pppppppp';
      Ldap_Base VarChar2(256):='cn=users,dc=zzz,dc=gggggg,dc=ttt';
      BEGIN
      Retval := -1;
      --filedata := 'oidtest2;oidtest2;none@nowhere.com';
      filedata := p_username || ';' || p_password || ';' || p_email;
      plg_debug ('ADD users to Oracle Internet Directory :');
      plg_debug (RPAD('LDAP Host ',25,' ') || ': '|| Ldap_Host);
      plg_debug (RPAD('LDAP Port ',25,' ') || ': '|| Ldap_Port);
      -- 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);
      plg_debug (RPAD('Ldap session',25,' ')||':'||RAWTOHEX(SUBSTR(My_Session,1,8))||'(returned init)');
      -- Bind to the directory
      retval := DBMS_LDAP.simple_bind_s(My_Session,Ldap_User,Ldap_Passwd);
      plg_debug(RPAD('simple_bind_s Returns ',25,' ') || ': '|| TO_CHAR(retval));
      -- Process New Entry in the database
      FOR i IN 1 .. 1
      LOOP
      plg_debug(filedata);
      -- Create and setup attribute array for the New entry
      My_Array := DBMS_LDAP.create_mod_array(14);
      -- RDN to be - cn="FIRST_NAME LAST_NAME"
      My_Vals_Int(1) := instr(filedata,';',1,1);
      My_Vals(1) := substr(filedata,1,My_Vals_Int(1) - 1);
      My_CN := My_Vals(1);
      plg_debug(My_CN);
      DBMS_LDAP.populate_mod_array(My_Array,DBMS_LDAP.MOD_ADD,'cn',My_Vals);
      DBMS_LDAP.populate_mod_array(My_Array,DBMS_LDAP.MOD_ADD,'sn',My_Vals);
      DBMS_LDAP.populate_mod_array(My_Array,DBMS_LDAP.MOD_ADD,'uid',My_Vals);
      My_Vals(1) := 'top';
      My_Vals(2) := 'person';
      My_Vals(3) := 'organizationalPerson';
      My_Vals(4) := 'inetOrgPerson';
      My_Vals(5) := 'orcluser';
      My_Vals(6) := 'orcluserv2';
      DBMS_LDAP.populate_mod_array(My_Array,DBMS_LDAP.MOD_ADD,'objectclass',My_Vals);
      My_Vals.DELETE;
      My_Vals_Int(2) := instr(filedata,';',1,2);
      My_Vals(1) := substr(filedata,My_Vals_Int(1)+1,My_Vals_Int(2) - My_Vals_Int(1) - 1);
      plg_debug(My_Vals(1));
      DBMS_LDAP.populate_mod_array(My_Array,DBMS_LDAP.MOD_ADD,'userpassword',My_Vals);
      My_Vals_Int(3) := instr(filedata,';',1,3);
      My_Vals(1) := substr(filedata,My_Vals_Int(2)+1,My_Vals_Int(2) - My_Vals_Int(1) - 1);
      plg_debug(My_Vals(1));
      DBMS_LDAP.populate_mod_array(My_Array,DBMS_LDAP.MOD_ADD,'mail',My_Vals);
      -- DN for Entry to be Added under 'ldap_base'
      My_DN := 'cn=' || My_CN ||','|| ldap_base ;
      plg_debug(RPAD('Adding Entry for DN ',25,' ') || ': ['|| My_DN || ']');
      -- Add new Entry to ldap directory
      retval := DBMS_LDAP.add_s(My_Session,My_DN,My_Array);
      plg_debug(RPAD('add_s Returns ',25,' ') || ': '|| TO_CHAR(retval));
      -- Beginning of Modifies
      -----------------------------------------------------------------------
      -- ModifyPriv
      -----------------------------------------------------------------------
      My_ModArray := DBMS_LDAP.create_mod_array(2);
      My_Modval(1) := 'cn=' || My_CN || ',cn=users, dc=nl,dc=oracle,dc=com';
      DBMS_LDAP.populate_mod_array(My_ModArray, DBMS_LDAP.MOD_ADD,'uniquemember', My_Modval);
      retval := DBMS_LDAP.modify_s(My_Session,'cn=OracleDASGroupPriv,cn=Groups,cn=OracleContext,dc=nl,dc=oracle,dc=com',My_ModArra
      y);
      plg_debug ('cn=OracleDASGroupPriv,cn=Groups,cn=OracleContext,dc=nl,dc=oracle,dc=com'||' returns:' || retval);
      DBMS_LDAP.free_mod_array(My_ModArray);
      My_Modval.DELETE;
      My_ModArray := DBMS_LDAP.create_mod_array(2);
      My_Modval(1) := 'cn=' || My_CN || ',cn=users, dc=nl,dc=oracle,dc=com';
      DBMS_LDAP.populate_mod_array(My_ModArray, DBMS_LDAP.MOD_ADD,'uniquemember', My_Modval);
      retval := DBMS_LDAP.modify_s(My_Session,'cn=OracleDASEditGroup,cn=Groups,cn=OracleContext,dc=nl,dc=oracle,dc=com',My_ModArra
      y);
      plg_debug ('cn=OracleDASEditGroup,cn=Groups,cn=OracleContext,dc=nl,dc=oracle,dc=com'||' returns:' || retval);
      DBMS_LDAP.free_mod_array(My_ModArray);
      My_Modval.DELETE;
      My_ModArray := DBMS_LDAP.create_mod_array(2);
      My_Modval(1) := 'cn=' || My_CN || ',cn=users, dc=nl,dc=oracle,dc=com';
      DBMS_LDAP.populate_mod_array(My_ModArray, DBMS_LDAP.MOD_ADD,'uniquemember',My_Modval);
      retval := DBMS_LDAP.modify_s(My_Session,'cn=OracleDASUserPriv,cn=Groups,cn=OracleContext,dc=nl,dc=oracle,dc=com',My_ModArray
      );
      plg_debug ('cn=OracleDASUserPriv,cn=Groups,cn=OracleContext,dc=nl,dc=oracle,dc=com'||' returns:' || retval);
      DBMS_LDAP.free_mod_array(My_ModArray);
      My_Modval.DELETE;
      My_ModArray := DBMS_LDAP.create_mod_array(2);
      My_Modval(1) := 'cn=' || My_CN || ',cn=users, dc=nl,dc=oracle,dc=com';
      DBMS_LDAP.populate_mod_array(My_ModArray, DBMS_LDAP.MOD_ADD,'uniquemember', My_Modval);
      retval := DBMS_LDAP.modify_s(My_Session,'cn=OracleDASConfiguration,cn=Groups,cn=OracleContext,dc=nl,dc=oracle,dc=com',My_Mod
      Array);
      plg_debug ('cn=OracleDASConfiguration,cn=Groups,cn=OracleContext,dc=nl,dc=oracle,dc=com'||' returns:' ||retval);
      DBMS_LDAP.free_mod_array(My_ModArray);
      My_Modval.DELETE;
      My_ModArray := DBMS_LDAP.create_mod_array(2);
      My_Modval(1) := 'cn=' || My_CN || ',cn=users, dc=nl,dc=oracle,dc=com';
      DBMS_LDAP.populate_mod_array(My_ModArray, DBMS_LDAP.MOD_ADD,'uniquemember',
      My_Modval);
      retval := DBMS_LDAP.modify_s(My_Session,'cn=OracleDASDeleteUser,cn=Groups,cn=OracleContext,dc=nl,dc=oracle,dc=com',My_ModArr
      ay);
      plg_debug ('cn=OracleDASDeleteUser,cn=Groups,cn=OracleContext,dc=nl,dc=oracle,dc=com'||' returns:' || retval);
      DBMS_LDAP.free_mod_array(My_ModArray);
      My_Modval.DELETE;
      My_ModArray := DBMS_LDAP.create_mod_array(2);
      My_Modval(1) := 'cn=' || My_CN || ',cn=users, dc=nl,dc=oracle,dc=com';
      DBMS_LDAP.populate_mod_array(My_ModArray, DBMS_LDAP.MOD_ADD,'uniquemember', My_Modval);
      retval := DBMS_LDAP.modify_s(My_Session,'cn=OracleDASEditUser,cn=Groups,cn=OracleContext,dc=nl,dc=oracle,dc=com',My_ModArray
      );
      plg_debug ('cn=OracleDASEditUser,cn=Groups,cn=OracleContext,dc=nl,dc=oracle,dc=com'||' returns:' || retval);
      DBMS_LDAP.free_mod_array(My_ModArray);
      My_Modval.DELETE;
      My_ModArray := DBMS_LDAP.create_mod_array(2);
      My_Modval(1) := 'cn=' || My_CN || ',cn=users, dc=nl,dc=oracle,dc=com';
      DBMS_LDAP.populate_mod_array(My_ModArray, DBMS_LDAP.MOD_ADD,'uniquemember',My_Modval);
      retval := DBMS_LDAP.modify_s(My_Session,'cn=OracleDASCreateGroup,cn=Groups,cn=OracleContext,dc=nl,dc=oracle,dc=com',My_ModAr
      ray);
      plg_debug ('cn=OracleDASCreateGroup,cn=Groups,cn=OracleContext,dc=nl,dc=oracle,dc=com'||' returns:' || retval);
      DBMS_LDAP.free_mod_array(My_ModArray);
      My_Modval.DELETE;
      My_ModArray := DBMS_LDAP.create_mod_array(2);
      My_Modval(1) := 'cn=' || My_CN || ',cn=users, dc=nl,dc=oracle,dc=com';
      DBMS_LDAP.populate_mod_array(My_ModArray, DBMS_LDAP.MOD_ADD,'uniquemember',My_Modval);
      retval := DBMS_LDAP.modify_s(My_Session,'cn=OracleDASDeleteGroup,cn=Groups,cn=OracleContext,dc=nl,dc=oracle,dc=com',My_ModAr
      ray);
      plg_debug ('cn=OracleDASDeleteGroup,cn=Groups,cn=OracleContext,dc=nl,dc=oracle,dc=com'||' returns:' || retval);
      DBMS_LDAP.free_mod_array(My_ModArray);
      My_Modval.DELETE;
      My_ModArray := DBMS_LDAP.create_mod_array(2);
      My_Modval(1) := 'cn=' || My_CN || ',cn=users, dc=nl,dc=oracle,dc=com';
      DBMS_LDAP.populate_mod_array(My_ModArray, DBMS_LDAP.MOD_ADD,'uniquemember',My_Modval);
      retval := DBMS_LDAP.modify_s(My_Session,'cn=OracleDASCreateUser,cn=Groups,cn=OracleContext,dc=nl,dc=oracle,dc=com',My_ModArr
      ay);
      plg_debug ('cn=OracleDASCreateUser,cn=Groups,cn=OracleContext,dc=nl,dc=oracle,dc=com'||' returns:' || retval);
      DBMS_LDAP.free_mod_array(My_ModArray);
      My_Modval.DELETE;
      -----------------------------------------------------------------------
      -- ModifyGroup
      -----------------------------------------------------------------------
      My_ModArray := DBMS_LDAP.create_mod_array(2);
      My_Modval(1) := 'cn=' || My_CN || ',cn=users, dc=nl,dc=oracle,dc=com';
      DBMS_LDAP.populate_mod_array(My_ModArray, DBMS_LDAP.MOD_ADD,'uniquemember', My_Modval);
      retval := DBMS_LDAP.modify_s(My_Session,'cn=PORTLET_PUBLISHERS,cn=portal_groups,cn=groups,dc=nl,dc=oracle,dc=com',My_ModArra
      y);
      plg_debug ('cn=PORTLET_PUBLISHERS,cn=portal_groups,cn=groups,dc=nl,dc=oracle,dc=com'||' returns:' || retval);
      DBMS_LDAP.free_mod_array(My_ModArray);
      My_Modval.DELETE;
      My_ModArray := DBMS_LDAP.create_mod_array(2);
      My_Modval(1) := 'cn=' || My_CN || ',cn=users, dc=nl,dc=oracle,dc=com';
      DBMS_LDAP.populate_mod_array(My_ModArray, DBMS_LDAP.MOD_ADD,'uniquemember', My_Modval);
      retval := DBMS_LDAP.modify_s(My_Session,'cn=DBA,cn=portal_groups,cn=groups,dc=nl,dc=oracle,dc=com', My_ModArray);
      plg_debug ('cn=DBA,cn=portal_groups,cn=groups,dc=nl,dc=oracle,dc=com'||' returns:' || retval);
      DBMS_LDAP.free_mod_array(My_ModArray);
      My_Modval.DELETE;
      My_ModArray := DBMS_LDAP.create_mod_array(2);
      My_Modval(1) := 'cn=' || My_CN || ',cn=users, dc=nl,dc=oracle,dc=com';
      DBMS_LDAP.populate_mod_array(My_ModArray, DBMS_LDAP.MOD_ADD,'uniquemember', My_Modval);
      retval := DBMS_LDAP.modify_s(My_Session,'cn=PORTAL_ADMINISTRATORS,cn=portal_groups,cn=groups,dc=nl,dc=oracle,dc=com',My_ModA
      rray);
      plg_debug ('cn=PORTAL_ADMINISTRATORS,cn=portal_groups,cn=groups,dc=nl,dc=oracle,dc=com'||' returns:' || retval);
      DBMS_LDAP.free_mod_array(My_ModArray);
      My_Modval.DELETE;
      My_ModArray := DBMS_LDAP.create_mod_array(2);
      My_Modval(1) := 'cn=' || My_CN || ',cn=users, dc=nl,dc=oracle,dc=com';
      DBMS_LDAP.populate_mod_array(My_ModArray, DBMS_LDAP.MOD_ADD,'uniquemember', My_Modval);
      retval := DBMS_LDAP.modify_s(My_Session,'cn=PORTAL_DEVELOPERS,cn=portal_groups,cn=groups,dc=nl,dc=oracle,dc=com',My_ModArray
      );
      plg_debug ('cn=PORTAL_DEVELOPERS,cn=portal_groups,cn=groups,dc=nl,dc=oracle,dc=com'||' returns:' || retval);
      DBMS_LDAP.free_mod_array(My_ModArray);
      My_Modval.DELETE;
      -----------------------------------------------------------------------
      -- End of Modifies
      -----------------------------------------------------------------------
      -- Free attribute array (My_Array)
      DBMS_LDAP.free_mod_array(My_Array);
      END LOOP;
      retval := DBMS_LDAP.unbind_s(My_Session);
      EXCEPTION
      WHEN OTHERS THEN
      err := SQLERRM;
      num := SQLCODE;
      plg_debug(err);
      plg_debug(num);
      retval := DBMS_LDAP.unbind_s(My_Session);
      END;
      /

      After addusers procedure was created successfully, I ran it:

      exec addusers('oidtest2','oidtest2','oidtest2@nowhere.com');
      /

      But, I never got the result back. The plg_debug logs indicated that:

      retval := DBMS_LDAP.add_s(My_Session,My_DN,My_Array);

      went into an infinite loop and never came back. Not only this, this problem also caused the whole Oracle LDAP being locked up. I had to bounce the web and infrastructure apache instances to bring Oracle LDAP back.

      I tried two OracleiAS 9.0.4 instances on HP Unix and Redhat Linux and I got the same problem.

      But, when I used the same user data and ldapadd to do it, I inserted this oidtest2 user successfully.

      Any help will be appreciated.


        • 1. Re: DBMS_LDAP.add_s always go into infinite loop
          53473
          I figured out the problem.

          In customed ON-INSERT trigger of Oracle Forms, you can add your business logics and then call INSERT_RECORD to excute the default function of ON-INSERT trigger. But, in OID plugin customed when_add_replace procedure, if you add your business logics and call DBMS_LDAP.add_s to try to execute the default function of ldapadd, DBMS_LDAP.add_s actually calls your own customed when_add_replace procedure again instead of just simply adds an entry to OID. That created the infinite loop.

          • 2. Re: DBMS_LDAP.add_s always go into infinite loop
            783719
            Hi All,

            I used to the code given to create a User in the AD and was successful.

            but the user that is getting created in the Active Directory is disabled. Is there any mechanism that i can get this user enabled by using the following packages?

            DBMS_LDAP
            DBMS_LDAP_UTL

            Or is there any other mechanism that we can use to get this done?

            Thanks in advance.

            Best Regards,
            Indika

            Edited by: Indika Sampath Ranaweera on Nov 2, 2010 9:43 AM
            • 3. Re: DBMS_LDAP.add_s always go into infinite loop
              783719
              Hi All,

              Got it sorted out.

              There is an attribute attached to a user in the Active Directory (userAccountControl), which i was able to update using the DBMS_LDAP_UTL Package.

              The Procedure used is "set_user_properties".

              Best Regards,
              Indika
              • 4. Re: DBMS_LDAP.add_s always go into infinite loop
                139186
                Hi All,
                Can I copy an entry from AD to OID with PL/SQL ? We have AD with thousands of users and we only need to copy the few users. Can this be done using PL/SQL ?

                Thanks,
                Asha
                • 5. Re: DBMS_LDAP.add_s always go into infinite loop
                  783719
                  Yes, You will be able to do that, but you have easier methods as you can use OID provided features like bootstap to sync all the users.

                  when using that first you have to configure all the directories that consist of users to synchronize with OID.

                  Edited by: Indika Sampath Ranaweera on Jul 20, 2012 9:05 AM