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
You must be logged in to post a comment.