1 Reply Latest reply: Mar 11, 2013 1:50 PM by stefan nebesnak RSS

    dbms_ldap.get_values ora-01403: no data found

    oliver82
      Hi.

      I'm trying to get user information out of the active directory. The obtained information should be stored in an existing user table.
      The script looks something like that (left the general connection information etc. out)

      retval := DBMS_LDAP.SEARCH_S(
      ld => my_session
      , base => ldap_baseDN
      , scope => DBMS_LDAP.SCOPE_SUBTREE
      , filter => search_filter
      , attrs => res_attrs
      , attronly => 0
      , res => res_message
      );
      retval := DBMS_LDAP.COUNT_ENTRIES(my_session, res_message);
      IF retval = 1 THEN
      -- -----------------------------------------------------------------------------
      -- Write results out of string collection into single variables
      -- -----------------------------------------------------------------------------
      temp_value := DBMS_LDAP.GET_VALUES(my_session, res_message, 'employeeID');
      res_employeeid := temp_value(0);
      temp_value := DBMS_LDAP.GET_VALUES(my_session, res_message, 'givenName');
      res_firstname := temp_value(0);
      temp_value := DBMS_LDAP.GET_VALUES(my_session, res_message, 'sn');
      res_lastname := temp_value(0);
      temp_value := DBMS_LDAP.GET_VALUES(my_session, res_message, 'title');
      res_title := temp_value(0);
      temp_value := DBMS_LDAP.GET_VALUES(my_session, res_message, 'description');
      res_description := temp_value(0);
      temp_value := DBMS_LDAP.GET_VALUES(my_session, res_message, 'department');
      res_department := temp_value(0);
      temp_value := DBMS_LDAP.GET_VALUES(my_session, res_message, 'mail');
      res_mail := temp_value(0);
      temp_value := DBMS_LDAP.GET_VALUES(my_session, res_message, 'manager');
      res_manager := temp_value(0);
      temp_value := DBMS_LDAP.GET_VALUES(my_session, res_message, 'streetAddress');
      res_streetaddress := temp_value(0);
      temp_value := DBMS_LDAP.GET_VALUES(my_session, res_message, 'postalcode');
      res_postalcode := temp_value(0);
      temp_value := DBMS_LDAP.GET_VALUES(my_session, res_message, 'l');
      res_city := temp_value(0);
      temp_value := DBMS_LDAP.GET_VALUES(my_session, res_message, 'physicalDeliveryOfficeName');
      res_physicalDeliveryOfficeName := temp_value(0);
      END IF;
      -- --------------------------------------------------------------------------------------------------
      -- Update user in ou_global.users with obtained values from the directory.
      -- --------------------------------------------------------------------------------------------------
      UPDATE ou_global.users usr SET
      usr.employeeid = res_employeeid
      , usr.firstname = res_firstname
      , usr.lastname = res_lastname
      , usr.title = res_title
      , usr.description = res_description
      , usr.department = res_department
      , usr.email = res_mail
      , usr.manager = res_manager
      , usr.street = res_streetaddress
      , usr.postalcode = res_postalcode
      , usr.city = res_city
      , usr.physicaldeliveryofficename = res_physicaldeliveryofficename
      WHERE usr.user_name = upper(i.user_name);
      END LOOP;

      The script is working fine so far but if one of the attributes on the directory is empty the script stops with ORA-01403.
      Is there any chance to write a NULL value to the database when an attribute contains no data and how could that be realized?

      Thanks.
        • 1. Re: dbms_ldap.get_values ora-01403: no data found
          stefan nebesnak
          oliver82 wrote:
          <font style="color: red">...</font>
           
          temp_value := DBMS_LDAP.GET_VALUES(my_session, res_message, 'title');
          res_title := temp_value(0);
          <font style="color: red">...</font>
          The script is working fine so far but if one of the attributes on the directory is empty the script stops with <font style="background-color: #E7FA6B">ORA-01403</font>.
          Is there any chance to write a NULL value to the database when an attribute contains no data and how could that be realized?
          You can wrap the statement with it's own <pre>BEGIN .. EXCEPTION .. END</pre> block and catch <pre><font style="background-color: #E7FA6B">NO DATA FOUND</font></pre> exception.
          BEGIN
          temp_value := DBMS_LDAP.GET_VALUES(my_session, res_message, 'title');
          res_title := temp_value(0);
          EXCEPTION
            WHEN NO_DATA_FOUND THEN
            res_title := NULL;
          END;
          DBMS_LDAP.GET_VALUES function returns NULL if there are no values associated with the given attribute
          (temp_value = string collection)