Unable To Join Tables in Oracle Directory Services Manager (ODSM) for Oracle Virtual Directory (OVD) 11g (Doc ID 1162414.1)

Last updated on JULY 19, 2022

Applies to:

Symptoms

Oracle Virtual Directory (OVD) versions 11.1.1.1.0, 11.1.1.2.0, 11.1.1.3.0.

Trying to create a database (DB) adapter that will use 2 table join in OVD 11g.

a) Consider a situation where there are two tables, For example USERS1 and USERS2.

USERS1 with columns: CN, GIVENNAME, SN, PASSWORD, OU, TEL, EMAIL, LAST_NAME, FIRST_NAME

USERS2 with columns: CN, DEP, FAX

The goal is to use join the two tables using columns USERS1.CN = USERS2.CN.

In OVD 11.1.1.x  this cannot be done directly using the Wizard.  There is an Enhancement Request(ER) <Bug 9656184 > for this functionality.

This scenario is demonstrated in examples below :

b) Table USERS1 has two records:
-------------------------------
"CN","FIRST_NAME","LAST_NAME","EMAIL","TEL","OU","PASSWORD","SN","GIVENNAME"

"user1","Firstname1","Lastname1","Firstname1.Lastname1@mail","995577","ite","test","va","Firstname1"
"user2","Firstname2","lastname2","Firstname2.lastname2@mail","997755","it","test","st","Firstname2"

USERS2 have three records:
------------------------------
"NAME","DEP","FAX"
"user2","159","119977"
"user1","132","117799"
"alastname2","178","225588"

Refer to attached images,  1.PNG, 2.PNG, 3.PNG, 4.PNG

In order to join the two tables, one would expect two ldap entries for user1 and user2 as shown below:

cn=user2,cn=dbjoin,dc=oracle,dc=com
cn=user1,cn=dbjoin,dc=oracle,dc=com

However, attributes picked up from table USERS2 have following entries:

cn=user2,cn=dbjoin,dc=oracle,dc=com
departmentNumber=132
departmentNumber=159
departmentNumber=178

This is shown in image 5.PNG

One would expect to have an entry like this:
cn=user2,cn=dbjoin,dc=oracle,dc=com
departmentNumber=159

c)  In OVD 10.1.4.x this issue is not seen.  As an example, compare images 1.PNG, 2.PNG, 3.PNG, 4.PNG with 1_1014.PNG, 2_10104.PNG.  You will find that  the "DataBase Adapter Mapping: Build Joins" step that is present in 10.1.4.x version is missing in 11g version.

Cause

My Oracle Support provides customers with access to over a million knowledge articles and a vibrant support community of peers and Oracle experts.