Hallo Mozart,

folgenden Code verwende ich jede Nacht, um Benutzerdaten aus dem AD zu holen:

create or replace PROCEDURE CATCH_LDAP_USER
(
P_LETTER IN VARCHAR2, p_ldap_basedn in varchar2
) AS
ldap_host VARCHAR2(512); -- The LDAP Directory Host
ldap_port VARCHAR2(512); -- The LDAP Directory Port
ldap_user VARCHAR2(512); -- The LDAP Directory User
ldap_passwd VARCHAR2(512); -- The LDAP Directory Password
ldap_baseDN VARCHAR2(512); -- The starting (base) DN
retval PLS_INTEGER; -- Used for all API return values.
my_session DBMS_LDAP.SESSION; -- Used to store our LDAP Session
res_attrs DBMS_LDAP.STRING_COLLECTION; -- A String Collection used
-- to specify which
-- attributes to return
-- from the search.
search_filter VARCHAR2(512); -- A simple character string used to
-- store the filter (criteria) for
-- the search.
res_message DBMS_LDAP.MESSAGE; -- Used to store the message
-- (results) of the search.
temp_entry DBMS_LDAP.MESSAGE; -- Used to store entries retrieved
-- from the LDAP search to print
-- out at a later time.
entry_index PLS_INTEGER; -- Used as a counter while looping
-- through each entry. As we
-- retrieve an entry from the LDAP
-- directory, we increase the
-- counter by one.
temp_dn VARCHAR2(512); -- After each entry is retrieved
-- from the LDAP directory (from
-- the search), we want to use
-- this variable to extract, store
-- and print out the DN for each
-- entry.
temp_attr_name VARCHAR2(512); -- After retrieving an entry from
-- LDAP directory, we will want to
-- walk through all of the
-- returned attributes. This
-- variable will be used to store
-- each attribute name as we loop
-- through them.
temp_ber_elmt DBMS_LDAP.BER_ELEMENT;
attr_index PLS_INTEGER; -- Used as a counter variable for
-- each entry returned for each
-- entry.
temp_vals DBMS_LDAP.STRING_COLLECTION; -- Used to extract, store,
-- and print each of the
-- values from each
-- attribute.
--nimmt einen zu schreibenden Datensatz auf
v_testtab ad_user%rowtype;

--nimmt, den aktuellen datensatz zu einem benutzerkuerzel auf
--auf vergleichen zu koennen ob sich am ds etwas geaendert hat
v_testtab_db ad_user%rowtype;

--merkts sich, ob ein datensatz in der db gefunden wurde
v_found boolean;

--Funktion des Mitarbeiters
v_funktion varchar2(100);
BEGIN

DBMS_OUTPUT.ENABLE(1000000);

retval := -1;

-- -------------------------------------------------------------------------
-- Customize the following variables as needed.
-- -------------------------------------------------------------------------
ldap_host := 'ldap_host' ;
LDAP_PORT := '389';
ldap_user := 'CN eines berechtigten LDAP-Users';
ldap_passwd := 'password des users';

ldap_basedn := p_ldap_basedn;

-- -------------------------------------------------------------------------
-- Print out variables.
-- -------------------------------------------------------------------------
-- DBMS_OUTPUT.PUT_LINE('DBMS_LDAP Search Example');
-- DBMS_OUTPUT.PUT_LINE('---------------------------------------------------');
-- DBMS_OUTPUT.PUT_LINE(RPAD('LDAP Host ', 25, ' ') || ': ' || ldap_host);
-- DBMS_OUTPUT.PUT_LINE(RPAD('LDAP Port ', 25, ' ') || ': ' || ldap_port);
-- DBMS_OUTPUT.PUT_LINE(RPAD('LDAP User ', 25, ' ') || ': ' || ldap_user);
-- DBMS_OUTPUT.PUT_LINE(RPAD('LDAP Base ', 25, ' ') || ': ' || ldap_baseDN);

-- -------------------------------------------------------------------------
-- We want all exceptions from DBMS_LDAP to be raised.
-- -------------------------------------------------------------------------
DBMS_LDAP.USE_EXCEPTION := TRUE;

-- -------------------------------------------------------------------------
-- Obtain an LDAP session. The init() function initializes a session with an
-- LDAP server. This actually establishes a connection with the LDAP server
-- and returns a handle to the session which can be used for further
-- calls into the API.
-- -------------------------------------------------------------------------
my_session := DBMS_LDAP.INIT(ldap_host, ldap_port);

-- DBMS_OUTPUT.PUT_LINE (
-- RPAD('LDAP Session ', 25, ' ') || ': ' ||
-- RAWTOHEX(SUBSTR(my_session, 1, 16)) ||
-- ' - (returned from init)'
-- );

-- -------------------------------------------------------------------------
-- Bind to the directory. The function simple_bind_s can be used to perform
-- simple username/password based authentication to the directory server.
-- The username is a directory distinguished name. This function can be
-- called only after a valid LDAP session handle is obtained from a call to
-- DBMS_LDAP.init(). If the connection was successful, it will return:
-- DBMS_LDAP.SUCCESS. This function can raise the following exceptions:
-- invalid_session : Raised if the session handle ld is invalid.
-- general_error : For all other errors. The error string associated
-- with this exception will explain the error in
-- detail.
-- -------------------------------------------------------------------------
retval := DBMS_LDAP.SIMPLE_BIND_S(my_session, ldap_user, ldap_passwd);



-- DBMS_OUTPUT.PUT_LINE(
-- RPAD('simple_bind_s Returned ', 25, ' ') || ': '|| TO_CHAR(retval)
-- );

-- -------------------------------------------------------------------------
-- Before actually performing the sort, I want to setup the attributes I
-- would like returned. To do this, I declared a "String Collection" that
-- will be used to store all of the attributes I would like returned.
--
-- If I wanted to return all attributes, I would specify:
-- res_attrs(1) := '*';
--
-- If I wanted multiple (specified) attributes, I would specify:
-- res_attrs(1) := 'cn';
-- res_attrs(2) := 'telephoneNumber';
-- -------------------------------------------------------------------------

--Strasse
--res_attrs(1) := 'physicalDeliveryOfficeName';

--Benutzerkuerzel
res_attrs(1) := 'sAMAccountName';
--Nachname
res_attrs(2) := 'sn';
--Vorname
RES_ATTRS(3) := 'givenName';
-- Mail-Adresse
RES_ATTRS(4) := 'mail';
-- DN
RES_ATTRS(5) := 'distinguishedName';
-- Abteilung
RES_ATTRS(6) := 'department';
-- Telefon-Durchwahl
RES_ATTRS(7) := 'telephoneNumber';
-- Office-Number
RES_ATTRS(8) := 'physicalDeliveryOfficeName';
-- Manager
RES_ATTRS(9) := 'manager';
-- persnr
res_attrs(10) := 'employeeNumber';
-- Title
RES_ATTRS(11) := 'title';

-- -------------------------------------------------------------------------
-- Finally, before performing the actual search, I want to specify the
-- criteria I want to search on. This will be passed as the "filter"
-- parameter to the actual search.
--
-- If you wanted all of the entries in the directory to be returned,
-- you could simply specify:
-- search_filter := 'objectclass=*';
--
-- You could also refine your search my specify a criteria like the
-- following:
-- search_filter := 'cn=Jeff*';
-- -------------------------------------------------------------------------


-- LDAP - Suchen - Suchfilter
-- Suchfilter Filtertyp
----------------------------------------------------------------
-- (sn=Muller) Gleicheitsfilter
-- (sn=Mu*) Substring Filter
-- (sn~=Muller) Ahnlichkeitsfilter
-- (telephoneNumer=*) Existenzfilter
-- (age<=21) Bereichsfilter >= und <=
-- (!(age<=21)) Ersatz fur (age>21)
-- (&(sn=Muller)(L=Nurnberg)) UND-Verknupfung
-- (|(sn=Muller)(sn=Huber)) ODER-Verknupfung

-- Zeichen Escape Sequenz
----------------------------------------------------------------
-- * \2A
-- ( \28
-- ) \29
-- \ \5C
-- NUL \00
--search_filter := 'cn=*R*';
search_filter := '(&(objectclass=user) (sn=' || P_LETTER || '*) ) ';






-- -------------------------------------------------------------------------
-- Finally, let's issue the search. The function search_s performs a
-- synchronous search in the LDAP server. It returns control to the PL/SQL
-- environment only after all of the search results have been sent by the
-- server or if the search request is 'timed-out' by the server.
--
-- Let's first explain some of the incoming parameters:
-- ld : A valid LDAP session handle.
-- base : The dn of the entry at which to start the search.
-- scope : One of SCOPE_BASE (0x00)
-- SCOPE_ONELEVEL (0x01)
-- SCOPE_SUBTREE (0x02)
-- indicating the scope of the search.
-- filter : A character string representing the search filter. The
-- value NULL can be passed to indicate that the filter
-- "(objectclass=*)" which matches all entries is to be
-- used.
-- attrs : A collection of strings indicating which attributes to
-- return for each matching entry. Passing NULL for this
-- parameter causes all available user attributes to be
-- retrieved. The special constant string NO_ATTRS ("1.1")
-- MAY be used as the only string in the array to indicate
-- that no attribute types are to be returned by the server.
-- The special constant string ALL_USER_ATTRS ("*") can be
-- used in the attrs array along with the names of some
-- operational attributes to indicate that all user
-- attributes plus the listed operational attributes are to
-- be returned.
-- attronly : A boolean value that MUST be zero if both attribute types
-- and values are to be returned, and non-zero if only types
-- are wanted.
-- res : This is a result parameter which will contain the results
-- of the search upon completion of the call. If no results
-- are returned, res is set to NULL.
--
-- Now let's look at the two output parameters:
-- PLS_INTEGER
-- (function return) : DBMS_LDAP.SUCCESS if the search operation
-- succeeded. An exception is raised in all other
-- cases.
-- res (OUT parameter) : If the search succeeded and there are entries,
-- this parameter is set to a NON-NULL value
-- which can be used to iterate through the
-- result set.
-- -------------------------------------------------------------------------




retval := DBMS_LDAP.SEARCH_S(
ld => my_session
, base => ldap_baseDN
, scope => DBMS_LDAP.SCOPE_SUBTREE
, filter => search_filter
, attrs => res_attrs
, attronly => 0
, res => res_message
);

-- DBMS_OUTPUT.PUT_LINE(
-- RPAD('search_s Returned ', 25, ' ') || ': ' || TO_CHAR(retval)
-- );
-- DBMS_OUTPUT.PUT_LINE (
-- RPAD('LDAP Message ', 25, ' ') || ': ' ||
-- RAWTOHEX(SUBSTR(res_message, 1, 16)) ||
-- ' - (returned from search_s)'
-- );

-- -------------------------------------------------------------------------
-- Anzahl der gefunden Eintraege zaehlen
-- -------------------------------------------------------------------------
retval := DBMS_LDAP.COUNT_ENTRIES(my_session, res_message);
-- DBMS_OUTPUT.PUT_LINE(
-- RPAD('Number of Entries ', 25, ' ') || ': ' || TO_CHAR(retval)
-- );
-- DBMS_OUTPUT.PUT_LINE('---------------------------------------------------');


-- -------------------------------------------------------------------------
-- Cursor auf ersten Eintrag
-- -------------------------------------------------------------------------
temp_entry := DBMS_LDAP.FIRST_ENTRY(my_session, res_message);
entry_index := 1;

-- -------------------------------------------------------------------------
-- Aufsteigend durch die Eintraege im Resultset laufen
-- -------------------------------------------------------------------------
WHILE temp_entry IS NOT NULL LOOP
-- DBMS_OUTPUT.PUT_LINE('===============NEUER DATENSATZ===================');
-- ---------------------------------------------------------------------
-- Aktuellen Datensatz anzeigen
-- ---------------------------------------------------------------------
--wir merken uns den Destinctish Name, der die Quelle des DS angibt
TEMP_DN := DBMS_LDAP.GET_DN(MY_SESSION, TEMP_ENTRY);
-- DBMS_OUTPUT.PUT_LINE (' dn: ' || temp_dn);


temp_attr_name := DBMS_LDAP.FIRST_ATTRIBUTE(
my_session
, temp_entry
, temp_ber_elmt
);

--Variablen zurueck setzen
v_testtab := null;


attr_index := 1;
WHILE temp_attr_name IS NOT NULL LOOP
-- dbms_output.put_line('ATTRIBUT ' || temp_attr_name || '~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~');


temp_vals := DBMS_LDAP.GET_VALUES(my_session, temp_entry, temp_attr_name);
IF temp_vals.COUNT > 0 THEN
FOR i IN temp_vals.FIRST..temp_vals.LAST LOOP
-- dbms_output.put_line('ELEMENT: ' || temp_attr_name);


--Test: DS zusammenbauen
if upper(temp_attr_name) = 'SAMACCOUNTNAME' then
V_TESTTAB.USERNAME := SUBSTR(TEMP_VALS(I), 1, 200);
-- DBMS_OUTPUT.PUT_LINE('Anmeldename: ' || V_TESTTAB.USERNAME);
elsif upper(temp_attr_name) = 'SN' then
V_TESTTAB.SURNAME := SUBSTR(TEMP_VALS(I), 1, 200);
-- DBMS_OUTPUT.PUT_LINE('Nachname: ' || V_TESTTAB.SURNAME);
elsif upper(temp_attr_name) = 'GIVENNAME' then
v_testtab.FIRSTNAME := SUBSTR(temp_vals(i), 1, 200);
-- DBMS_OUTPUT.PUT_LINE('Vorname: ' || v_testtab.Firstname);
elsif upper(temp_attr_name) = 'MAIL' then
V_TESTTAB.MAIL := SUBSTR(TEMP_VALS(I), 1, 200);
-- DBMS_OUTPUT.PUT_LINE('Vorname: ' || v_testtab.Mail);
elsif upper(temp_attr_name) = 'DISTINGUISHEDNAME' then
V_TESTTAB.DN := SUBSTR(TEMP_VALS(I), 1, 200);
-- DBMS_OUTPUT.PUT_LINE('Vorname: ' || v_testtab.DN);
elsif upper(temp_attr_name) = 'DEPARTMENT' then
V_TESTTAB.DEPARTMENT := SUBSTR(TEMP_VALS(I), 1, 200);
-- DBMS_OUTPUT.PUT_LINE('Vorname: ' || v_testtab.Department);
ELSIF UPPER(TEMP_ATTR_NAME) = 'TELEPHONENUMBER' THEN
--DBMS_OUTPUT.PUT_LINE('tel: ' || temp_vals(I));
V_TESTTAB.PHONE := SUBSTR(TEMP_VALS(I), 1, 200);
ELSIF UPPER(TEMP_ATTR_NAME) = 'PHYSICALDELIVERYOFFICENAME' THEN
--DBMS_OUTPUT.PUT_LINE('office: ' || temp_vals(I));
v_testtab.office := substr(temp_vals(i), 1, 200);
ELSIF UPPER(TEMP_ATTR_NAME) = 'TITLE' THEN
--DBMS_OUTPUT.PUT_LINE('office: ' || temp_vals(I));
V_TESTTAB.TITLE := SUBSTR(TEMP_VALS(I), 1, 200);
ELSIF UPPER(TEMP_ATTR_NAME) = 'EMPLOYEENUMBER' THEN
--DBMS_OUTPUT.PUT_LINE('emp_number: ' || temp_vals(I));
-- es gibt im AD Emp-Nummern, die kein Number-Format haben :-(
begin
V_TESTTAB.EMPLOYEENUMBER := SUBSTR(TEMP_VALS(I), 1, 200);

exception
when others then
v_testtab.employeenumber := '-1';
end;
ELSIF UPPER(TEMP_ATTR_NAME) = 'MANAGER' THEN
--DBMS_OUTPUT.PUT_LINE('manager: ' || temp_vals(I));
-- es wird nachgeschaut, ob es einen managereintrag gibt
-- dann dessen id ablegen
BEGIN
--dbms_output.put_line(temp_vals(I));
select max(id)
into v_testtab.manager_id
from AD_USER
where dn = temp_vals(I);

exception
when NO_DATA_FOUND then
v_found := false;
end;

end if;
--Ende Test

--
-- DBMS_OUTPUT.PUT_LINE(
-- RPAD(' ' || temp_attr_name, 19, ' ') ||
-- ': ' || SUBSTR(temp_vals(i), 1, 200)
-- );
END LOOP;



END IF;
temp_attr_name := DBMS_LDAP.NEXT_ATTRIBUTE( my_session
, temp_entry
, temp_ber_elmt);
attr_index := attr_index + 1;

END LOOP;

temp_entry := DBMS_LDAP.NEXT_ENTRY(my_session, temp_entry);
-- DBMS_OUTPUT.PUT_LINE('===============ENDE DES DATENSATZES==============');
entry_index := entry_index + 1;

-- aus dem Displayname die Funktion des Mitarbeiters herausfiltern
--v_funktion := substr(v_testtab.Anzeigename, instr(v_testtab.Anzeigename,',',-1,1)+2);
--dbms_output.put_line(v_funktion || ' --- ' || v_testtab.anzeigename);

--Datensatz in Tabelle schreiben, nach Pruefung ob es die Person schon gibt
begin
--Variablen zurueck setzen
v_found := null;

--Datensaetze aus DB-Holen
begin
select *
into v_testtab_db
from AD_USER
where username = v_testtab.username;

exception
when NO_DATA_FOUND then
v_found := false;
-- dbms_output.put_line('Noch keine Datensaetze mit diesem Kuerzel');
-- when others then
-- dbms_output.put_line('FEHLER BEI SUCHE DES BENUTZERS IN DER DATENBANK' || SQLERRM);
end;

-- dbms_output.put_line('testtab:'|| v_testtab.Org || ' testtab_db:' || v_testtab_db.org);
--Gibt es keinen DS, wird der neue Benutzerdatensatz gespeichert
IF V_FOUND = FALSE THEN

insert into ad_user values v_testtab;
commit;
-- dbms_output.put_line('Datensatz gespeichert' || V_TESTTAB.SURNAME);
--gibt es den Datensatz, aber er hat sich geaendert, wird der Datensatz komplett geupdatet
ELSIF V_TESTTAB.FIRSTNAME <> V_TESTTAB_DB.FIRSTNAME or
V_TESTTAB.SURNAME <> V_TESTTAB_DB.SURNAME or
V_TESTTAB.MAIL <> V_TESTTAB_DB.MAIL or
V_TESTTAB.DN <> V_TESTTAB_DB.DN or
V_TESTTAB.DEPARTMENT <> V_TESTTAB_DB.DEPARTMENT or
V_TESTTAB.PHONE <> V_TESTTAB_DB.PHONE or
V_TESTTAB.OFFICE <> V_TESTTAB_DB.OFFICE or
V_TESTTAB.MANAGER_ID <> V_TESTTAB_DB.MANAGER_ID OR
V_TESTTAB.EMPLOYEENUMBER <> V_TESTTAB_DB.EMPLOYEENUMBER or
V_TESTTAB_DB.MAIL is null or
V_TESTTAB_DB.DN is null or
V_TESTTAB_DB.DEPARTMENT is null or
V_TESTTAB_DB.PHONE is null or
V_TESTTAB_DB.OFFICE is null or
V_TESTTAB_DB.MANAGER_ID IS NULL OR
V_TESTTAB_DB.EMPLOYEENUMBER is null
then

-- dbms_output.put_line(v_testtab_db.username || '-' || v_testtab.surname || '-' || v_testtab_db.phone || '-' || v_testtab_db.office);

update ad_user
set firstname = v_testtab.firstname,
SURNAME = V_TESTTAB.SURNAME,
MAIL = V_TESTTAB.MAIL,
DN = V_TESTTAB.DN,
DEPARTMENT = V_TESTTAB.DEPARTMENT,
PHONE = V_TESTTAB.PHONE,
OFFICE = V_TESTTAB.OFFICE,
MANAGER_ID = V_TESTTAB.MANAGER_ID,
EMPLOYEENUMBER = V_TESTTAB.EMPLOYEENUMBER
where upper(username) = upper(v_testtab_db.username);
commit;
-- dbms_output.put_line('Datensatz geupdatet');
--Ansonsten mache mal gar nix
else
null;
end if;


-- exception
-- when others then
-- dbms_output.put_line('Fehler im Block fuer DML der Benutzerdaten' || SQLERRM);
end;

-- dbms_output.put_line('GESPEICHERT: ');
-- dbms_output.put_line('KUERZEL: ' || v_testtab.Benutzerkuerzel);
-- dbms_output.put_line('VORNAME: ' || v_testtab.Vorname);
-- dbms_output.put_line('NACHNAME: ' || v_testtab.Nachname);
-- dbms_output.put_line('TELEFON: ' || v_testtab.Telefon);
-- dbms_output.put_line('EMAIL: ' || v_testtab.Email);
-- dbms_output.put_line('Oe: ' || v_testtab_db.org);
--Ende DS schreiben

END LOOP;

-- -------------------------------------------------------------------------
-- Unbind from the directory
-- -------------------------------------------------------------------------
retval := DBMS_LDAP.UNBIND_S(my_session);
-- DBMS_OUTPUT.PUT_LINE(RPAD(
-- 'unbind_res Returned ', 25, ' ') || ': ' ||
-- TO_CHAR(retval)
-- );

-- -------------------------------------------------------------------------
-- Handle Exceptions
-- -------------------------------------------------------------------------
EXCEPTION
WHEN OTHERS THEN
-- DBMS_OUTPUT.PUT_LINE(' Error code : ' || TO_CHAR(SQLCODE));
DBMS_OUTPUT.PUT_LINE(' Error Message : ' || SQLERRM);
-- DBMS_OUTPUT.PUT_LINE(' Exception encountered .. exiting');

END CATCH_LDAP_USER;

--
Best regards,
Carsten mailto:carsten.cerny@cc13.com
My blog [cc13.com]