How to use DBMS_LDAP (part 6: Modifying)

Table of Contents

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

Modifying data in the LDAP hierarchy

The DBMS_LDAP package allows you to write as well as read data from the directory. However, it does not provide an API for manipulating the schemas within the tree. That is, you can add, delete, or modify records of predefined structures but you may not create new structure types of your own.

In my previous articles in this series I used publicly available servers for my examples. As you might expect, those are read-only. So, in this article my examples will be based off of the LDAP structures used for Oracle database name resolution. These could be from Oracle’s own OID product, or some other LDAP server hosting the same schema.

Inserting a new record

To insert a new record, you might think of it similar to a SQL insert into an index-organized table. That is, unlike a normal heap-table, where rows can go anywhere in the table; in an IOT, or an LDAP structure, each row can only exist within a specific location. Thus an LDAP insert consists of defining the unique key of the record, i.e. where the new record will go, as well as the content fields of the new record.

The where portion consists of the DN as we’ve used in searching from previous sessions. The content is defined by an array of attributes. Each attribute is itself an array. In the example below, each attribute has only a single value, so as the modification array is constructed, each array adds an array of just one element. Since every node within a directory must have an object class, there will always be at least on attribute populated in the modification array to set that value.

In the case of an Oracle Net Service Name, the object class is “orclNetService”. In order to be a useful record it must also include the name to be resolved, as well as the TNS description to which the name resolves. So, in this example I am inserting a new name “test_name” that resolves to “(DESCRIPTION=(ADDRESS=(PROTOCOL=TCP)(HOST=myserver)(PORT=1521))(CONNECT_DATA=(SERVICE_NAME=myservice)))”

DECLARE
    v_session      DBMS_LDAP.session;
    v_result       PLS_INTEGER;
    v_attributes   DBMS_LDAP.mod_array;
    v_strings      DBMS_LDAP.string_collection;
BEGIN
    DBMS_LDAP.use_exception := FALSE;
    v_session := DBMS_LDAP.init(hostname => 'ldap.example.net', portnum => DBMS_LDAP.port);

    v_result :=
        DBMS_LDAP.simple_bind_s(ld       => v_session,
                                dn       => 'uid=myuserid,ou=users,dc=example,dc=net',
                                passwd   => 'secret_password');

    IF v_result != DBMS_LDAP.success
    THEN
        DBMS_OUTPUT.put_line('Bind Result: ' || v_result || ' ' || DBMS_LDAP.err2string(v_result));
    ELSE
        v_attributes := DBMS_LDAP.create_mod_array(num => 3);

        v_strings(1) := 'orclNetService';
        DBMS_LDAP.populate_mod_array(modptr     => v_attributes,
                                     mod_op     => DBMS_LDAP.mod_add,
                                     mod_type   => 'objectclass',
                                     modval     => v_strings);

        v_strings(1) := 'test_name';
        DBMS_LDAP.populate_mod_array(modptr     => v_attributes,
                                     mod_op     => DBMS_LDAP.mod_add,
                                     mod_type   => 'cn',
                                     modval     => v_strings);

        v_strings(1) :=
            '(DESCRIPTION=(ADDRESS=(PROTOCOL=TCP)(HOST=myserver)(PORT=1521))(CONNECT_DATA=(SERVICE_NAME=myservice)))';
        DBMS_LDAP.populate_mod_array(modptr     => v_attributes,
                                     mod_op     => DBMS_LDAP.mod_add,
                                     mod_type   => 'orclNetDescString',
                                     modval     => v_strings);

        v_result :=
            DBMS_LDAP.add_s(ld        => v_session,
                            entrydn   => 'cn=test_name,cn=OracleContext,dc=example,dc=net',
                            modptr    => v_attributes);

        IF v_result = DBMS_LDAP.success
        THEN
            DBMS_OUTPUT.put_line('Add successful');
        ELSE
            DBMS_OUTPUT.put_line('Add Result: ' || v_result || ' ' || DBMS_LDAP.err2string(v_result));
        END IF;

        DBMS_LDAP.free_mod_array(modptr => v_attributes);
        v_result := DBMS_LDAP.unbind_s(v_session);
    END IF;
END;
/
Add successful

If you run the block twice, you should get an error on the second run for trying to create two entries at the same DN.

Add Result: 68 Already exists

Deleting an old record

Deleting a record is fairly simple. To make a correlation with SQL, you simply specify the unique key to delete that record. DBMS_LDAP does not provide an API for mass deletion. If you wanted to delete multiple records, you could call the delete_s function for each DN, or, if you didn’t know them, you could perform a search as shown in prior chapters, and then retrieve the DN from each search result to do the delete.

DECLARE
    v_session          DBMS_LDAP.session;
    v_result           PLS_INTEGER;
BEGIN
    DBMS_LDAP.use_exception := FALSE;
    v_session := DBMS_LDAP.init(hostname => 'ldap.example.net', portnum => DBMS_LDAP.port);

    v_result :=
        DBMS_LDAP.simple_bind_s(ld       => v_session,
                                dn       => 'uid=myuserid,ou=users,dc=example,dc=net',
                                passwd   => 'secret_password');

    IF v_result != DBMS_LDAP.success
    THEN
        DBMS_OUTPUT.put_line('Bind Result: ' || v_result || ' ' || DBMS_LDAP.err2string(v_result));
    ELSE
        v_result := DBMS_LDAP.delete_s(ld => v_session, entrydn => 'cn=test_name,cn=OracleContext,dc=example,dc=net');

        IF v_result = DBMS_LDAP.success
        THEN
            DBMS_OUTPUT.put_line('Delete successful');
        ELSE
            DBMS_OUTPUT.put_line('Delete Result: ' || v_result || ' ' || DBMS_LDAP.err2string(v_result));
        END IF;

        v_result := DBMS_LDAP.unbind_s(v_session);
    END IF;
END;
/
Delete successful

If you run the block twice, you should get an error on the second run for trying to delete a DN that doesn’t exist.

Delete Result: 32 No such object

Modifying a record

Modifying a record in LDAP can but doesn’t always correlate with a SQL update. At the simplest level, you can specify the unique key, the DN, as you would with a delete, but instead of removing the record, you can replace the value of an attribute with a new value. In SQL terms this would be analogous to something like this:

update my_table set my_attribute = new_value where id = my_dn;

You can update more than one attribute by populating the modification array with more than one replace command. Here I update our test record with a new TNS description pointing to another server with a different service name for that listener.

DECLARE
    v_session      DBMS_LDAP.session;
    v_result       PLS_INTEGER;
    v_attributes   DBMS_LDAP.mod_array;
    v_strings      DBMS_LDAP.string_collection;
BEGIN
    DBMS_LDAP.use_exception := FALSE;
    v_session := DBMS_LDAP.init(hostname => 'ldap.example.net', portnum => DBMS_LDAP.port);

    v_result := DBMS_LDAP.simple_bind_s(ld => v_session, dn => 'uid=myuserid,ou=users,dc=example,dc=net', passwd => 'secret_password');

    IF v_result != DBMS_LDAP.success
    THEN
        DBMS_OUTPUT.put_line('Bind Result: ' || v_result || ' ' || DBMS_LDAP.err2string(v_result));
    ELSE
        v_attributes := DBMS_LDAP.create_mod_array(num => 1);

        v_strings(1) :=
            '(DESCRIPTION=(ADDRESS=(PROTOCOL=TCP)(HOST=myotherserver)(PORT=1521))(CONNECT_DATA=(SERVICE_NAME=myotherservice)))';
        DBMS_LDAP.populate_mod_array(modptr     => v_attributes,
                                     mod_op     => DBMS_LDAP.mod_replace,
                                     mod_type   => 'orclNetDescString',
                                     modval     => v_strings);

        v_result :=
            DBMS_LDAP.modify_s(ld        => v_session,
                               entrydn   => 'cn=test_name,cn=OracleContext,dc=example,dc=net',
                               modptr    => v_attributes);

        IF v_result = DBMS_LDAP.success
        THEN
            DBMS_OUTPUT.put_line('Modify successful');
        ELSE
            DBMS_OUTPUT.put_line('Modify Result: ' || v_result || ' ' || DBMS_LDAP.err2string(v_result));
        END IF;

        DBMS_LDAP.free_mod_array(modptr => v_attributes);
        v_result := DBMS_LDAP.unbind_s(v_session);
    END IF;
END;
/
Modify successful

While that replace looks like a fairly standard update, correlating well with SQL, you can also modify records to remove attributes. In SQL terms, that would be like removing a column from just one row of a table. Not setting it to NULL, but actually removing the column entirely.

Or, maybe a closer analogy, if your table had a collection type for a column and you deleted an element from that collection.

Even that though still doesn’t quite compare, because if you delete all attributes of a certain type, there is no place holder attribute left behind in an LDAP directory. But, if you had a collection type in a table, the column would still exist, but have a null or empty collection there.

So, the lesson here is that LDAP modify operations should be seen as a more complex operation.

Next I’ll perform an equivalent update as before, except instead of doing a replace, I’ll add a new description attribute and then delete the old one. This might seem like a violation because there will temporarily be two descriptions for a single name, but modify_s function is essentially atomic, no matter how many attribute modifications are applied. So, the LDAP constraint ensuring a single description for a name isn’t applied until after the all of the modifications are completed.

This example is merely for illustration not a recommendation. If you have a single value to update, using replace is more efficient than insert and delete. Also note this example assumes the directory is populated after the initial insert above, not after the other modify example.

DECLARE
    v_session      DBMS_LDAP.session;
    v_result       PLS_INTEGER;
    v_attributes   DBMS_LDAP.mod_array;
    v_strings      DBMS_LDAP.string_collection;
BEGIN
    DBMS_LDAP.use_exception := FALSE;
    v_session := DBMS_LDAP.init(hostname => 'ldap.example.net', portnum => DBMS_LDAP.port);

    v_result :=
        DBMS_LDAP.simple_bind_s(ld       => v_session,
                                dn       => 'uid=myuserid,ou=users,dc=example,dc=net',
                                passwd   => 'secret_password');

    IF v_result != DBMS_LDAP.success
    THEN
        DBMS_OUTPUT.put_line('Bind Result: ' || v_result || ' ' || DBMS_LDAP.err2string(v_result));
    ELSE
        v_attributes := DBMS_LDAP.create_mod_array(num => 2);

        v_strings(1) :=
            '(DESCRIPTION=(ADDRESS=(PROTOCOL=TCP)(HOST=myotherserver)(PORT=1521))(CONNECT_DATA=(SERVICE_NAME=myotherservice)))';
        DBMS_LDAP.populate_mod_array(modptr     => v_attributes,
                                     mod_op     => DBMS_LDAP.mod_add,
                                     mod_type   => 'orclNetDescString',
                                     modval     => v_strings);

        v_strings(1) :=
            '(DESCRIPTION=(ADDRESS=(PROTOCOL=TCP)(HOST=myserver)(PORT=1521))(CONNECT_DATA=(SERVICE_NAME=myservice)))';
        DBMS_LDAP.populate_mod_array(modptr     => v_attributes,
                                     mod_op     => DBMS_LDAP.mod_delete,
                                     mod_type   => 'orclNetDescString',
                                     modval     => v_strings);


        v_result :=
            DBMS_LDAP.modify_s(ld        => v_session,
                               entrydn   => 'cn=test_name,cn=OracleContext,dc=example,dc=net',
                               modptr    => v_attributes);

        IF v_result = DBMS_LDAP.success
        THEN
            DBMS_OUTPUT.put_line('Modify successful');
        ELSE
            DBMS_OUTPUT.put_line('Modify Result: ' || v_result || ' ' || DBMS_LDAP.err2string(v_result));
        END IF;

        DBMS_LDAP.free_mod_array(modptr => v_attributes);
        v_result := DBMS_LDAP.unbind_s(v_session);
    END IF;
END;

If the block is run twice you’ll get an error trying to add the same description value twice.

Modify Result: 20 Type or value exists

Rearranging the delete to occur first won’t help, because the delete is for the old description value. So, if you put the delete first and run it twice, you’ll get a different error, but the block will still fail.

Modify Result: 16 No such attribute

In addition to the inefficiency using two modifications instead of one, these errors also help to illustrate why using the replace modification is a better choice when it applies.

Of course, if you have a record with a multi-value attribute, it may be appropriate to delete one attribute and add another instead of doing a replace. For example, updating a person’s record, deleting one or more old email records and adding new ones. Or, not doing a replace at all, maybe adding children to a parent or making changes to employees of a department. The modification action may consist only of add operations or only of deletes. During a business’s reorganization a department might change names using a replace, change managers, also using a replace, and then add several employees and remove others. All of which could be executed with a single modify_s call to the department’s record.

This concludes my series on DBMS_LDAP. I hope you found it enlightening and the examples useful starting points for your own applications. I know I learned more in the process of writing it.

Questions on this chapter or any of the previous chapters are always welcome.