很久没有在blog上写东西了,前一段时间在忙结婚的事。人生的一件大事总算是完成了。 婚假结束之后,公司安排让协助其他部门的同事开发几个存储过程,接到任务以后去了解了一下情况说是要开发一个oracle跟LDAP同步更新的一个东西,由于没有接触过LDAP,大概google了一下,说这是一个类似于表格的东西,但是严格来说没有列和行的分布,有些类似于注册表或者资源管理器当中的那种分级的数据。经过多方查找资料,实际上网上的资料还有官方的资料都比较的多,有很多可以借鉴的。下面就把具体的过程贴出来供大家参考:
CREATE OR REPLACE PROCEDURE PSTATIC_SYNC
AS
-- Adjust as necessary.
l_ldap_host VARCHAR2(256) := '10.10.220.15'; --LDAP服务器的IP地址
l_ldap_port VARCHAR2(256) := '389'; --LDAP服务器的默认端口
l_ldap_user VARCHAR2(256) := 'uid=binder,dc=sctel,dc=com,dc=cn'; --用户名
l_ldap_passwd VARCHAR2(256) := 'stat919'; --密码
l_ldap_base VARCHAR2(256) := 'cn=users,dc=scdbc,dc=com,dc=cn'; --要访问的资料范围 主要通过cn=xxx来进行区别
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;
BEGIN
-- Choose to raise exceptions.
DBMS_LDAP.USE_EXCEPTION := TRUE;
-- Connect to the 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);
-- Get all attributes
l_attrs(1) := '*'; -- retrieve all attributes
l_retval := DBMS_LDAP.search_s(ld => l_session,
base => l_ldap_base,
scope => DBMS_LDAP.SCOPE_SUBTREE,
filter => 'objectclass=*',
attrs => l_attrs,
attronly => 0,
res => l_message);
IF DBMS_LDAP.count_entries(ld => l_session, msg => l_message) > 0 THEN
-- Get all the entries returned by our search.
l_entry := DBMS_LDAP.first_entry(ld => l_session,
msg => l_message);
<< entry_loop >>
WHILE l_entry IS NOT NULL LOOP
-- Get all the attributes for this 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 LOOP
-- Get all the values for this attribute.
BEGIN
l_vals := DBMS_LDAP.get_values (ld => l_session,
ldapentry => l_entry,
attr => 'cn');
EXECUTE IMMEDIATE 'TRUNCATE TABLE member_cn';
<< values_loop >>
FOR i IN l_vals.FIRST .. l_vals.LAST LOOP
INSERT INTO MEMBER_CN (USER_NAME) VALUES (l_vals(i));
COMMIT;
END LOOP values_loop;
EXCEPTION WHEN
OTHERS THEN
NULL;
END;
由于LDAP数据里面存在为0字节的信息,所以遇到这种情况不能选取所有的信息,只能有选择的获取需要查看的信息
------------------------首先获取用户ID---------------------------------------------
BEGIN
l_vals := DBMS_LDAP.get_values (ld => l_session,
ldapentry => l_entry,
attr => 'uid');
--循环之前先截断接受UID值的表
EXECUTE IMMEDIATE 'TRUNCATE TABLE member_uid';
--开始获取UID的值
<< values_loop >>
FOR i IN l_vals.FIRST .. l_vals.LAST LOOP
INSERT INTO MEMBER_UID (USER_ID) VALUES (l_vals(i));
COMMIT;
END LOOP values_loop;
EXCEPTION WHEN
OTHERS THEN
NULL;
END;
-------------------------获取用户手机号码--------------------------------------------
BEGIN
l_vals := DBMS_LDAP.get_values (ld => l_session,
ldapentry => l_entry,
attr => 'mobile');
--截断后接受手机信息
EXECUTE IMMEDIATE 'TRUNCATE TABLE member_mobile';
<< values_loop >>
FOR i IN l_vals.FIRST .. l_vals.LAST LOOP
INSERT INTO MEMBER_MOBILE (MOBILE) VALUES (l_vals(i));
COMMIT;
END LOOP values_loop;
EXCEPTION WHEN
OTHERS THEN
NULL;
END;
-------------------------------公司名称-------------------------------------
BEGIN
l_vals := DBMS_LDAP.get_values (ld => l_session,
ldapentry => l_entry,
attr => 'companyName');
--截断后插入数据
EXECUTE IMMEDIATE 'TRUNCATE TABLE member_companyName';
<< values_loop >>
FOR i IN l_vals.FIRST .. l_vals.LAST LOOP
INSERT INTO member_companyname (company_name) VALUES (l_vals(i));
COMMIT;
END LOOP values_loop;
EXCEPTION WHEN
OTHERS THEN
NULL;
END;
-----------------------------获取公司ID---------------------------------------
BEGIN
l_vals := DBMS_LDAP.get_values (ld => l_session,
ldapentry => l_entry,
attr => 'companyId');
--截断后插入数据
EXECUTE IMMEDIATE 'TRUNCATE TABLE member_companyid';
<< values_loop >>
FOR i IN l_vals.FIRST .. l_vals.LAST LOOP
INSERT INTO member_companyid (company_id) VALUES (l_vals(i));
COMMIT;
END LOOP values_loop;
EXCEPTION WHEN
OTHERS THEN
NULL;
END;
--获取下一组用户信息
l_attr_name := DBMS_LDAP.next_attribute(ld => l_session,
ldapentry => l_entry,
ber_elem => l_ber_element);
END LOOP attibutes_loop; --结束信息获取循环
INSERT INTO
user_sync (
user_name,
user_id,
mobile,
company_name,
company_id )
SELECT (SELECT user_name FROM member_cn WHERE rownum=1),
(SELECT user_id FROM member_uid WHERE rownum=1),
(SELECT mobile FROM member_mobile WHERE rownum=1),
(SELECT company_name FROM member_companyname where rownum=1),
(SELECT company_id FROM member_companyid where rownum=1)
FROM dual;
COMMIT;
l_entry := DBMS_LDAP.next_entry(ld => l_session,
msg => l_entry);
END LOOP entry_loop;
END IF;
-- Disconnect from the LDAP server.
l_retval := DBMS_LDAP.unbind_s(ld => l_session);
--sync user info.
MERGE INTO user_info i
USING user_sync s
ON (i.user_id = s.user_id)
WHEN MATCHED THEN
UPDATE SET
i.user_name=s.user_name,
i.mobile=s.mobile,
i.company_name=s.company_name,
i.company_id=s.company_id
WHEN NOT MATCHED THEN
INSERT VALUES (s.user_name,s.user_id,s.user_id,s.mobile,'Y',s.company_name,s.company_id);
COMMIT;
DELETE FROM user_info WHERE user_id IN (
SELECT user_id FROM user_info
MINUS
SELECT user_id FROM user_sync) AND is_init = 'Y';
COMMIT;
EXECUTE IMMEDIATE 'TRUNCATE TABLE user_sync';
END;
来自 “ ITPUB博客 ” ,链接:http://blog.itpub.net/12216142/viewspace-694079/,如需转载,请注明出处,否则将追究法律责任。
转载于:http://blog.itpub.net/12216142/viewspace-694079/