This page (revision-1) was last changed on 29-Nov-2024 16:16 by UnknownAuthor

Only authorized users are allowed to rename pages.

Only authorized users are allowed to delete pages.

Page revision history

Version Date Modified Size Author Changes ... Change note

Page References

Incoming links Outgoing links
Oracle SQL To Access LDAP
Oracle SQL To Access LDAP

Version management

Difference between version and

At line 1 added 181 lines
!!! Overview
We are not DBA experts but we have often been involved with integration work using [{$pagename}].
We shows some [Example code] to get you started with [{$pagename}]
!! DBMS_LDAP - Accessing LDAP From PL/SQL [1]
The DBMS_LDAP package is a PL/SQL API to enable programatic searches and modifications of data within LDAP directories. In this article I'll demonstrate a simple LDAP search.
! Loading the DBMS_LDAP package
The DBMS_LDAP package is not loaded by default. To install it run the following script as SYS.
{{{
SQL> @$ORACLE_HOME/rdbms/admin/catldap.sql
}}}
!! Connect And Authenticate
The first step in any LDAP interaction is to connect to the LDAP server and authenticate yourself. This is done using the init and simple_bind_s functions.
{{{
l_session := DBMS_LDAP.init(hostname => l_ldap_host,
portnum => l_ldap_port);
l_retval := DBMS_LDAP.simple_bind_s(ld => l_session,
dn => l_ldap_user,
passwd => l_ldap_passwd);
}}}
!! [Search LDAP Directory|SearchRequest]
Once connected we can search the directory.
{{{
l_attrs(1) := '*'; -- retrieve all attributes
l_retval := DBMS_LDAP.search_s(ld => l_session,
base => l_ldap_base,
scope => DBMS_LDAP.SCOPE_SUBTREE,
filter => 'objectclass=*',
attrs => l_attrs,
attronly => 0,
res => l_message);
}}}
The starting point for the search (base), depth of the search (scope), the attributes searched for (attrs) and the filter can be modified to build complex searches.
!! Loop Through [Search Responses]
An LDAP search returns a [Search Responses] which can be looped through.
__Note:__ We do not check for [SearchResultReference]s within this code.
The code assumes all the [Search Responses] are [SearchResultEntry]s.
{{{
IF DBMS_LDAP.count_entries(ld => l_session, msg => l_message) > 0 THEN
-- Get all the entries returned by our search.
l_entry := DBMS_LDAP.first_entry(ld => l_session,
msg => l_message);
<< entry_loop >>
WHILE l_entry IS NOT NULL LOOP
...
...
l_entry := DBMS_LDAP.next_entry(ld => l_session,
msg => l_entry);
END LOOP entry_loop;
END IF;
}}}
!! Loop Through Attributes
For each entry we loop through the associated attributes.
{{{
l_attr_name := DBMS_LDAP.first_attribute(ld => l_session,
ldapentry => l_entry,
ber_elem => l_ber_element);
<< attributes_loop >>
WHILE l_attr_name IS NOT NULL LOOP
...
...
l_attr_name := DBMS_LDAP.next_attribute(ld => l_session,
ldapentry => l_entry,
ber_elem => l_ber_element);
END LOOP attibutes_loop;
}}}
!! Loop Through Values
Finally, we retrieve the values associated with the attribute.
{{{
<< values_loop >>
FOR i IN l_vals.FIRST .. l_vals.LAST LOOP
DBMS_OUTPUT.PUT_LINE('ATTIBUTE_NAME: ' || l_attr_name || ' = ' || SUBSTR(l_vals(i),1,200));
END LOOP values_loop;
}}}
!! Put It All Together
If we put all these stages together we get the following.
{{{
SET SERVEROUTPUT ON SIZE 1000000
DECLARE
-- Adjust as necessary.
l_ldap_host VARCHAR2(256) := 'server01.tshcomputing.com';
l_ldap_port VARCHAR2(256) := '389';
l_ldap_user VARCHAR2(256) := 'cn=orcladmin';
l_ldap_passwd VARCHAR2(256) := 'password';
l_ldap_base VARCHAR2(256) := 'cn=Users,dc=tshcomputing,dc=com';
l_retval PLS_INTEGER;
l_session DBMS_LDAP.session;
l_attrs DBMS_LDAP.string_collection;
l_message DBMS_LDAP.message;
l_entry DBMS_LDAP.message;
l_attr_name VARCHAR2(256);
l_ber_element DBMS_LDAP.ber_element;
l_vals DBMS_LDAP.string_collection;
BEGIN
-- Choose to raise exceptions.
DBMS_LDAP.USE_EXCEPTION := TRUE;
-- Connect to the LDAP server.
l_session := DBMS_LDAP.init(hostname => l_ldap_host,
portnum => l_ldap_port);
l_retval := DBMS_LDAP.simple_bind_s(ld => l_session,
dn => l_ldap_user,
passwd => l_ldap_passwd);
-- Get all attributes
l_attrs(1) := '*'; -- retrieve all attributes
l_retval := DBMS_LDAP.search_s(ld => l_session,
base => l_ldap_base,
scope => DBMS_LDAP.SCOPE_SUBTREE,
filter => 'objectclass=*',
attrs => l_attrs,
attronly => 0,
res => l_message);
IF DBMS_LDAP.count_entries(ld => l_session, msg => l_message) > 0 THEN
-- Get all the entries returned by our search.
l_entry := DBMS_LDAP.first_entry(ld => l_session,
msg => l_message);
<< entry_loop >>
WHILE l_entry IS NOT NULL LOOP
-- Get all the attributes for this entry.
DBMS_OUTPUT.PUT_LINE('---------------------------------------');
l_attr_name := DBMS_LDAP.first_attribute(ld => l_session,
ldapentry => l_entry,
ber_elem => l_ber_element);
<< attributes_loop >>
WHILE l_attr_name IS NOT NULL LOOP
-- Get all the values for this attribute.
l_vals := DBMS_LDAP.get_values (ld => l_session,
ldapentry => l_entry,
attr => l_attr_name);
<< values_loop >>
FOR i IN l_vals.FIRST .. l_vals.LAST LOOP
DBMS_OUTPUT.PUT_LINE('ATTIBUTE_NAME: ' || l_attr_name || ' = ' || SUBSTR(l_vals(i),1,200));
END LOOP values_loop;
l_attr_name := DBMS_LDAP.next_attribute(ld => l_session,
ldapentry => l_entry,
ber_elem => l_ber_element);
END LOOP attibutes_loop;
l_entry := DBMS_LDAP.next_entry(ld => l_session,
msg => l_entry);
END LOOP entry_loop;
END IF;
-- Disconnect from the LDAP server.
l_retval := DBMS_LDAP.unbind_s(ld => l_session);
DBMS_OUTPUT.PUT_LINE('L_RETVAL: ' || l_retval);
END;
/
}}}
! [Oracle SQL Workshop to Bind with LDAP]
We show how to use [Oracle SQL Workshop to Bind with LDAP]
----
[#1] - Largely gathered from [http://www.oracle-base.com/]
!! More Information
There might be more information for this subject on one of the following:
[{ReferringPagesPlugin before='*' after='\n' }]
This page (revision-1) was last changed on 29-Nov-2024 16:16 by UnknownAuthorTop