Twitter Linkedin RSS
Close
LDAP / Oracle

How to use DBMS_LDAP (part 5: Timeouts)

Sean D. StuberLeave a comment

Table of Contents

  1. Introduction
  2. Establishing a Connection
  3. Searching Hierarchical Data
  4. Browsing Attributes
  5. Timeouts (this page)
  6. Modifying Data

Timeouts

LDAP servers are designed to be fast; but it is still possible for an action to take a long time. A deep sub-tree search with an open search criteria could require from seconds to even minutes to return data if the directory was large and no other criteria limited the results.

Thus clients are able to put timeout limits on their requests to abort if they take too long. The public servers I’ve been using are relatively small in scope and/or have result limits on them so they tend to be fast but we’ll create an intentionally expensive search and time it. I’ll use the approximate equality operator to do an expensive search into a deep and wide subtree.

DECLARE
    v_result           PLS_INTEGER;
    v_session          DBMS_LDAP.session;
    v_search_attrs     DBMS_LDAP.string_collection;
    v_search_results   DBMS_LDAP.MESSAGE;
    v_start            TIMESTAMP WITH TIME ZONE;
    v_end              TIMESTAMP WITH TIME ZONE;
BEGIN
    DBMS_LDAP.use_exception := TRUE;

    v_session := DBMS_LDAP.init(hostname => 'ldap.andrew.cmu.edu', portnum => DBMS_LDAP.port);

    v_search_attrs(1) := '*';

    v_start := SYSTIMESTAMP;
    v_result :=
        DBMS_LDAP.search_s(ld         => v_session,
                           base       => 'dc=cmu,dc=edu',
                           scope      => DBMS_LDAP.scope_subtree,
                           filter     => 'maillocaladdress~=ayushb',
                           attrs      => v_search_attrs,
                           attronly   => 0,
                           res        => v_search_results);
    v_end := SYSTIMESTAMP;

    DBMS_OUTPUT.put_line(v_end - v_start);
    v_result := DBMS_LDAP.unbind_s(v_session);
END;
/

+000000000 00:00:20.977627000

So, just over 20 seconds to do that search. If that is too long, a time out can be created using the DBMS_LDAP.TIMEVAL type. Then instead of using SEARCH_S, we use SEARCH_ST – “T” for timed. If the timeout is exceeded an exception will be raised. In order to more clearly capture the time, I’ll disable exceptions and examine the result code of the SEARCH_ST function instead. We’ll put a 2 second time limit on the search and check the timing when we’re done.

DECLARE
    v_result           PLS_INTEGER;
    v_session          DBMS_LDAP.session;
    v_search_attrs     DBMS_LDAP.string_collection;
    v_search_results   DBMS_LDAP.MESSAGE;
    v_start            TIMESTAMP WITH TIME ZONE;
    v_end              TIMESTAMP WITH TIME ZONE;
    v_timeout          DBMS_LDAP.timeval;
BEGIN
    DBMS_LDAP.use_exception := FALSE;

    v_session := DBMS_LDAP.init(hostname => 'ldap.andrew.cmu.edu', portnum => DBMS_LDAP.port);

    v_search_attrs(1) := '*';

    v_timeout.seconds := 2;

    v_start := SYSTIMESTAMP;
    v_result :=
        DBMS_LDAP.search_st(ld         => v_session,
                            base       => 'dc=cmu,dc=edu',
                            scope      => DBMS_LDAP.scope_subtree,
                            filter     => 'maillocaladdress~=ayushb',
                            attrs      => v_search_attrs,
                            attronly   => 0,
                            tv         => v_timeout,
                            res        => v_search_results);
    v_end := SYSTIMESTAMP;

    DBMS_OUTPUT.put_line('Error code: ' || v_result || ' ' || DBMS_LDAP.err2string(v_result));
    DBMS_OUTPUT.put_line(v_end - v_start);
    v_result := DBMS_LDAP.unbind_s(v_session);
END;
/

Error code: 85 Timed out
+000000000 00:00:02.000382000

As expected, the search quit after 2 seconds and exited.

The TIMEVAL type is actually a record of two fields: seconds and microseconds.

    TYPE TIMEVAL IS RECORD
      ( seconds  PLS_INTEGER,
        useconds PLS_INTEGER
      );

Either or both fields may be populated. If both are populated the timeout value will be the sum of the two times. As in the following example where seconds is populated with 1 and useconds with 1.5 million, for a total timeout of 2.5 seconds.

DECLARE
    v_result           PLS_INTEGER;
    v_session          DBMS_LDAP.session;
    v_search_attrs     DBMS_LDAP.string_collection;
    v_search_results   DBMS_LDAP.MESSAGE;
    v_start            TIMESTAMP WITH TIME ZONE;
    v_end              TIMESTAMP WITH TIME ZONE;
    v_timeout          DBMS_LDAP.timeval;
BEGIN
    DBMS_LDAP.use_exception := FALSE;

    v_session := DBMS_LDAP.init(hostname => 'ldap.andrew.cmu.edu', portnum => DBMS_LDAP.port);

    v_search_attrs(1) := '*';

    v_timeout.seconds := 1;
    v_timeout.useconds := 1500000;

    v_start := SYSTIMESTAMP;
    v_result :=
        DBMS_LDAP.search_st(ld         => v_session,
                            base       => 'dc=cmu,dc=edu',
                            scope      => DBMS_LDAP.scope_subtree,
                            filter     => 'maillocaladdress~=ayushb',
                            attrs      => v_search_attrs,
                            attronly   => 0,
                            tv         => v_timeout,
                            res        => v_search_results);
    v_end := SYSTIMESTAMP;

    DBMS_OUTPUT.put_line('Error code: ' || v_result || ' ' || DBMS_LDAP.err2string(v_result));
    DBMS_OUTPUT.put_line(v_end - v_start);
    v_result := DBMS_LDAP.unbind_s(v_session);
END;
/

Error code: 85 Timed out
+000000000 00:00:02.500799000

While the datatype of each value is PLS_INTEGER, which accepts negative values, if either value is negative you will receive an error code 1027 (Unknown error.)
Similarly if both values are left unpopulated (both NULL) a 1027 error will be returned.

If exceptions are enabled then the following exception will be raised.

ORA-31207: DBMS_LDAP: PL/SQL - Invalid LDAP search time value.

If both values are set to 0, that is treated as no timelimit. The search will run to completion or the server’s limit.

Leave a Reply