Re: Access Active Directory data with a view

From: willy <w.tadema_at_planet.nl>
Date: 15 Aug 2006 07:38:17 -0700
Message-ID: <1155652697.108027.220040@p79g2000cwp.googlegroups.com>

Dear Sybrand,

Thanks for your suggestions. I'm working on Oracle 10i. I've altered my original script. The results seem to be okay. Would you be so kind to comment on my alterations, since I'm not familiar with PL/SQL?

create or replace type o_gebrnm_regnr as object (   gebrnm varchar2(15),
  regnr varchar2(9)
)
/

create or replace type t_gebrnm_regnr as table of o_gebrnm_regnr /

create or replace function f_gebrnm_regnr return t_gebrnm_regnr   pipelined
as

  l_ldap_host    VARCHAR2(256) := 'xxx';
  l_ldap_port    VARCHAR2(256) := '389';
  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;

  a_gebrnm_regnr o_gebrnm_regnr := o_gebrnm_regnr(null,null); 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_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
            a_gebrnm_regnr.gebrnm := substr(l_vals(i),1,15);
          ELSIF l_attr_name='employeeNumber' THEN
            a_gebrnm_regnr.regnr := 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;

      pipe row (a_gebrnm_regnr);
      a_gebrnm_regnr := o_gebrnm_regnr(null,null);

      l_entry := DBMS_LDAP.next_entry(ld  => l_session,
                                      msg => l_entry);

    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);

  return;
end;
/

create or replace view v_ActiveDirectory as select * from table(cast(f_gebrnm_regnr() as t_gebrnm_regnr)); Received on Tue Aug 15 2006 - 09:38:17 CDT