Materialized View with OID users data

Sometimes you need access users data of OID to integrate with other systems, follow bellow how to do:

 

1. Create Types :

CREATE OR REPLACE TYPE LDAP_USU_TABLE is table of LDAP_USU_ROW;
/
CREATE OR REPLACE TYPE LDAP_USU_ROW
as object (login       varchar2(200),
           name        varchar2(200),
           mail        varchar2(200),
           phone       varchar2(200));
/

 

2. Create Function to acquire data of OID :

CREATE OR REPLACE function GET_LDAP_USU
return LDAP_USU_TABLE
is
  ldap_usu         LDAP_USU_TABLE := LDAP_USU_TABLE(LDAP_USU_ROW(NULL,
                                                                 NULL,
                                                                 NULL,
                                                                 NULL));
  retval           PLS_INTEGER;
  my_session       DBMS_LDAP.session;
  my_attrs         DBMS_LDAP.string_collection;
  my_message       DBMS_LDAP.message;
  my_entry         DBMS_LDAP.message;
  entry_index      PLS_INTEGER;
  my_dn            VARCHAR2(256);
  my_attr_name     VARCHAR2(256);
  my_ber_elmt      DBMS_LDAP.ber_element;
  attr_index       PLS_INTEGER;
  i                PLS_INTEGER;
  my_vals          DBMS_LDAP.STRING_COLLECTION ;
  ldap_host        VARCHAR2(256);
  ldap_port        VARCHAR2(256);
  ldap_user        VARCHAR2(256);
  ldap_passwd      VARCHAR2(256);
  ldap_base        VARCHAR2(256);
  v_login          varchar2(200);
  v_nome           varchar2(200);
  v_mail           varchar2(200);
  v_setor          varchar2(200);
  v_tel            varchar2(200);
  b_first          boolean := TRUE;
 BEGIN
  retval         := -1;
  -- Customization
  ldap_host  := 'oid.help2ora.com';
  ldap_port  := '389';
  ldap_user  := 'cn=orcladmin';
  ldap_passwd:= 'welcome1';
  ldap_base  := 'cn=Users,dc=help2ora,dc=com';
  -- end of customizable settings
  -- Choosing exceptions to be raised by DBMS_LDAP library.
  DBMS_LDAP.USE_EXCEPTION := TRUE;
  my_session := DBMS_LDAP.init(ldap_host,ldap_port);
  -- bind to the directory
  retval := DBMS_LDAP.simple_bind_s(my_session,
             ldap_user, ldap_passwd);
  -- issue the search
  my_attrs(1) := 'sn';
  my_attrs(2) := 'cn';
  my_attrs(3) := 'mail';
  my_attrs(5) := 'telephonenumber';
  retval := DBMS_LDAP.search_s(my_session, ldap_base,
                               dbms_ldap.SCOPE_SUBTREE,
                               'objectclass=inetOrgPerson',
                               ---'(&(objectclass=inetOrgPerson)(!(givenname=*computer*)))',
                               my_attrs,
                               0,
                               my_message);
  -- count the number of entries returned
  retval := DBMS_LDAP.count_entries(my_session, my_message);
  -- get the first entry
  my_entry := DBMS_LDAP.first_entry(my_session, my_message);
  entry_index := 1;
  -- Loop through each of the entries one by one
  while my_entry IS NOT NULL
  loop
    my_dn := DBMS_LDAP.get_dn(my_session, my_entry);
    my_attr_name := DBMS_LDAP.first_attribute(my_session,my_entry,my_ber_elmt);
    attr_index := 1;
    while my_attr_name IS NOT NULL
    loop
      my_vals := DBMS_LDAP.get_values(my_session,my_entry,my_attr_name);
      if my_vals.COUNT > 0
      then
        FOR i in my_vals.FIRST..my_vals.LAST
        loop
          if (lower(my_attr_name) = 'telephonenumber') then
                    v_tel := upper(my_vals(i));
          elsif (lower(my_attr_name) = 'mail') then
                    v_mail := upper(my_vals(i));
          elsif (lower(my_attr_name) = 'cn') then
                    v_nome := upper(my_vals(i));
          elsif (lower(my_attr_name) = 'sn') then
                    v_login := upper(my_vals(i));
          end if;
       end loop;
      end if;
      my_attr_name := DBMS_LDAP.next_attribute(my_session,my_entry,my_ber_elmt);
      attr_index := attr_index +.1;
    end loop;
        if b_first then
           b_first := FALSE;
        else
           ldap_usu.extend;
        end if;
       ldap_usu(ldap_usu.last) := ldap_usu_row(v_login,
                                               v_nome,
                                               v_mail,
                                               v_tel);

    my_entry := DBMS_LDAP.next_entry(my_session, my_entry);
    entry_index := entry_index+1;
    v_tel := null;
    v_mail := null;
    v_nome := null;
    v_login := null;
  end loop;
  -- unbind from the directory
  retval := DBMS_LDAP.unbind_s(my_session);
  return(ldap_usu);
END;
/

 

3. Create Materialized View with 10 minutes refresh:

CREATE MATERIALIZED VIEW VW_USERS
TABLESPACE USERS
NOCACHE
NOLOGGING
NOCOMPRESS
NOPARALLEL
BUILD IMMEDIATE
REFRESH FORCE
START WITH TO_DATE('03-ago-2011 17:09:17','dd-mon-yyyy hh24:mi:ss')
NEXT SYSDATE + (10/60/24)
WITH PRIMARY KEY
AS
/* Formatted on 03/08/2011 17:07:13 (QP5 v5.115.810.9015) */
SELECT   *
  FROM   TABLE (CAST (get_ldap_usu () AS ldap_usu_table));

 

Good Luck !

 

Leave a Reply

1 Trackback to "Materialized View with OID users data"

  1. on 03/03/2012 at 9:45 AM