Managing TNS lookups in OpenLDAP via PL/SQL

I have an Oracle Express Edition (XE) database running on almost every pc in my home. It’s not so numerous that I can’t manage them by manipulation of TNSNAMES.ORA; but I always thought it would be nice to manage them centrally via LDAP service name lookup.

Unfortunately Oracle Internet Directory (OID) licensing is included with a valid Oracle Database license unless that database is XE. I don’t really need or want a full OID installation anyway. So, I looked into other LDAP alternatives and decided on OpenLDAP. It’s open source, free and I can install it on Linux or Windows. I chose Linux and followed the instructions here… without issue.

configuring one of the Windows XE installs to use my new linux server was simple enough

DIRECTORY_SERVERS= (mylinuxserver:389:636)

The next step was getting my databases defined in the ldap server. I could do it through ldif files as shown in the link above; but I thought it would be nicer to have a sql and pl/sql interface to the server. Oracle facilitates that with the DBMS_LDAP package. To make it even easier, I built a wrapper package for it.

First, set up a user to own the package and, since I’m using 11g XE I need to create an Access Control List to allow my owner to talk to my ldap/oid server. I’m using the default non-SSL port 389.

create user oidowner identified by pa55w0rd;
grant create session to oidowner;
grant create procedure to oidowner;
grant create type to oidowner;

DBMS_NETWORK_ACL_ADMIN.create_acl(acl => 'oid.xml',
description => 'ACL that lets users see oid server',
principal => 'OIDOWNER',
is_grant => TRUE,
privilege => 'connect'

DBMS_NETWORK_ACL_ADMIN.assign_acl('oid.xml', 'mylinuxserver',389,389);

Next I create my package and a supporting type to make listing the contents easier

create or replace type vcarray as table of varchar2(4000);

Package code is available from my dropbox

And finally, I add an entry for an XE installation on one of my PCs.


Then I check my work…

C:\oraclexe\app\oracle\product\11.2.0\server\network\ADMIN>tnsping seanxe

TNS Ping Utility for 32-bit Windows: Version - Production on 24-AUG-2013 13:38:16

Copyright (c) 1997, 2010, Oracle. All rights reserved.

Used parameter files:

Used LDAP adapter to resolve the alias
OK (0 msec)

But, I want a SQL interface to the LDAP entries. This is where the collection type defined before the package comes in.
Using the list_oid table function it’s easy to retrieve some of the entries for other databases I’ve created.

C:\oraclexe\app\oracle\product\11.2.0\server\network\ADMIN>sqlplus oidowner/pa55w0rd@seanxe

SQL*Plus: Release Production on Sat Aug 24 14:01:54 2013

Copyright (c) 1982, 2010, Oracle. All rights reserved.

Connected to:
Oracle Database 11g Express Edition Release - Production

SQL> set lines 1000
SQL> SELECT * FROM TABLE(sdsoid.list_oid('dc=home', 'mylinuxserver'));


Currently all entries are maintained and retrieved with the full connect descriptor. In my next release I’ll add support for parameterized entries. I have tested this code on 12c databases and it works without modification.

Questions and comments are welcome.


One Response to Managing TNS lookups in OpenLDAP via PL/SQL

  1. I’ve updated the authentication to use full distinguished names rather than constructing them on the fly based on the default path. I’ve updated the example usage to show the new usage.

    I also made a small change in searches for better efficiency.


Questions and Comments always welcome

Fill in your details below or click an icon to log in: Logo

You are commenting using your account. Log Out /  Change )

Google+ photo

You are commenting using your Google+ account. Log Out /  Change )

Twitter picture

You are commenting using your Twitter account. Log Out /  Change )

Facebook photo

You are commenting using your Facebook account. Log Out /  Change )


Connecting to %s

%d bloggers like this: