Re: Access Active Directory data with a view

willy wrote:
> I'm an Oracle newbie and I've got a question concerning Active
> Directory. By modifying the Oracle example script slightly I've managed
>
> to make a PL/SQL script to retrieve the sAMAccountName and
> employeenumber for users in our Active Directory.
>
> I'd like to make these data accessible with a single view.
> I now have a PL/SQL procedure which fills the table ActiveDirectory and
>
> on this table I've build a view.
> Can this be done in fewer steps? Or with only a view? Any help would be
>
> appreciated.
>
>
> The code of the procedure I've build is listed below.
>
>
> Willy Tadema
>
>
> SET SERVEROUTPUT ON
> DECLARE
>
>
> l_ldap_host VARCHAR2(256) := 'xxx';
> l_ldap_port VARCHAR2(256) := 'xxx';
> l_ldap_user VARCHAR2(256) := 'xxx';
> l_ldap_passwd VARCHAR2(256) := 'xxx';
> l_ldap_base VARCHAR2(256) := 'xxx';
> l_filter VARCHAR2(256) := 'sAMAccountName=T*'; -- Alleen
> userid's die beginnen met de letter T
>
>
> 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;
>
>
> TYPE t_gebruikersnaam IS TABLE OF VARCHAR2(15) INDEX BY
> BINARY_INTEGER;
> TYPE t_registratienr IS TABLE OF VARCHAR2(9) INDEX BY BINARY_INTEGER;
>
>
>
> l_gebruikersnaam t_gebruikersnaam;
> l_registratienr t_registratienr;
> entry_index PLS_INTEGER;
>
>
> BEGIN
> -- Foutafhandeling activeren
> DBMS_LDAP.USE_EXCEPTION := TRUE;
>
>
> -- Verbinding maken met de 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);
>
>
> -- Haal attributen op
> l_attrs(1) := 'sAMAccountName'; -- Haal USERID op
> l_attrs(2) := 'employeeNumber'; -- Haal employeeNumber op
> l_retval := DBMS_LDAP.search_s(ld => l_session,
> base => l_ldap_base,
> scope => DBMS_LDAP.SCOPE_SUBTREE,
> filter => l_filter,
> attrs => l_attrs,
> attronly => 0,
> res => l_message);
>
>
> IF DBMS_LDAP.count_entries(ld => l_session, msg => l_message) > 0
> THEN
> -- Ga naar de eerste entry van de geretourneerde resultaatset.
> l_entry := DBMS_LDAP.first_entry(ld => l_session,
> msg => l_message);
>
>
> entry_index := 1;
>
>
> << entry_loop >>
> WHILE l_entry IS NOT NULL LOOP
>
>
> -- Haal alle attributen op voor deze entry
> 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 AND l_attr_name
> IN('sAMAccountName','employeeNumber') LOOP
> -- Haal alle waarden op van dit attribuut
> 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));
>
>
> IF l_attr_name='sAMAccountName' THEN
> l_gebruikersnaam(entry_index) := substr(l_vals(i),1,15);
> ELSIF l_attr_name='employeeNumber' THEN
> l_registratienr(entry_index) := substr(l_vals(i),1,9);
> END IF;
>
>
> 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);
>
>
> IF (l_gebruikersnaam.count < entry_index OR
> l_registratienr.count <
> entry_index) THEN BEGIN
> IF l_gebruikersnaam.count < entry_index THEN
> l_gebruikersnaam(entry_index) := null;
> END IF;
> IF l_registratienr.count < entry_index THEN
> l_registratienr(entry_index) := null;
> END IF;
> END;
> END IF;
> entry_index := entry_index + 1;
>
>
> END LOOP entry_loop;
> END IF;
>
>
> -- Verbreek de verbinding met de LDAP server.
> l_retval := DBMS_LDAP.unbind_s(ld => l_session);
> DBMS_OUTPUT.PUT_LINE('L_RETVAL: ' || l_retval);
>
>
> -- Maak de tabel ActiveDirectory eerst leeg en voeg dan de nieuwe,
> actuele records toe
> --TRUNCATE TABLE ActiveDirectory;
> EXECUTE IMMEDIATE 'TRUNCATE TABLE ActiveDirectory';
> FORALL i IN l_gebruikersnaam.FIRST .. l_gebruikersnaam.LAST
> INSERT INTO ActiveDirectory VALUES (l_gebruikersnaam(i),
> l_registratienr(i));
> COMMIT;
>
>
> -- Verwijder de PL/SQL tabellen
> l_gebruikersnaam.DELETE;
> l_registratienr.DELETE;
>
>
> END;
> /

Willy,

The best idea (if on 9i or higher, why do people think their version is irrelevant) is
convert this procedure to a *pipelined* function, so the results are returned as a table, and you don't need the database table anymore. You would need to convert the two non-composite temporary tables, to one temporary table using a record type.

Hth

-- 
Sybrand Bakker
Senior Oracle DBA