Get Ldap data with SQL

Do you work with Oracle Database?
Have you ever thought that you'd rather use a plain Oracle table than dealing with LDAP?

If the answer is yes, keep reading.

With this tiny PL/SQL package (downloadable here) you can access an LDAP Directory, formulate a query and get a table result.

Follow this simple example:

• Download the package, unzip it and install it in any database schema. It needs only access (execute grant) to the DBMS_LDAP package.
• Try (first) this query:

select c1 from table(TL.ask('&(cn=SMITH*)',null,'C=IT', 'certificati.postecert.it', 'mail,cn,uid,sn' ));

• Then dare a bit more structured examples:

SELECT tl.f (c1, 1) mail, tl.f (c1, 2) cn, tl.f (c1, 3) u_id, tl.f (c1, 4) sn
  FROM TABLE (tl.ask ('&(cn=SMITH*)',   -- Query Conditions
                      NULL,             -- Unique ID Condition
                      'C=IT',           -- Base dn
                      'certificati.postecert.it', -- Ldap Address
                      'mail,cn,uid,sn'  -- Attributes
                     )
             );

SELECT tl.f (c1, 1) mail, tl.f (c1, 2) nomeresp, tl.f (c1, 3) cognomeresp,
       tl.f (c1, 4) description, tl.f (c1, 5) mails
  FROM TABLE (tl.ask ('&(CognomeResp=Blas*)',  -- Query Conditions
                      NULL,                    -- Unique ID Condition
                      'c=it',                  -- Base dn 
                      'indicepa.gov.it',       -- Ldap Address 
       -- Attributes (fields)
                      'mail,NomeResp,CognomeResp,description,mailS'                      )
             );

If you find it useful and you’d like technical details, just post a comment and we will try to accomplish.

Have fun