本文共 9744 字，大约阅读时间需要 32 分钟。
在一些比较注重权限的EBS项目中, 普通用户通常没有System Administrator权限, 只能由DBA给一个一个用户一个一个职责加, 纯手工的话很麻烦, 于是写了一段PLSQL脚本, 留着备用. 由于Oracle 10g尚不支持continue语句, 因此10g和11g分开写了.
1. Based on EBS R12.0.6/Oracle DB 10gR2
DECLARE -- script to add user responsibility for R12.0/10gR2 TYPE tab_user_list IS TABLE OF VARCHAR2 (100); TYPE arr_resp_list IS VARRAY (100) OF VARCHAR2 (100); -- user to be changed l_tab_user_list tab_user_list := tab_user_list ('LIAO' ,'KARL' ,'xx'); -- responsibility to be added l_arr_resp_list arr_resp_list := arr_resp_list ('system Administrator' ,'Purchasing Super User' ,'Application Administrator'); CURSOR cur_user ( pc_username IN VARCHAR2) IS SELECT fu.user_id ,fu.user_name FROM fnd_user fu WHERE fu.user_name = pc_username AND TRUNC (SYSDATE) BETWEEN TRUNC (NVL (fu.start_date, SYSDATE)) AND TRUNC (NVL (fu.end_date, SYSDATE)); TYPE tab_user IS TABLE OF cur_user%ROWTYPE; l_tab_user tab_user; CURSOR cur_resp ( pc_resp IN VARCHAR2) IS SELECT fa.application_id ,fa.application_short_name ,fr.responsibility_id ,fr.responsibility_name ,fr.responsibility_key ,fsg.security_group_key FROM fnd_application fa ,fnd_responsibility_vl fr ,fnd_security_groups fsg WHERE LOWER (fr.responsibility_name) = LOWER (pc_resp) AND fa.application_id = fr.application_id AND fr.data_group_id = fsg.security_group_id; TYPE tab_resp IS TABLE OF cur_resp%ROWTYPE; l_tab_resp tab_resp; expt_no_user EXCEPTION; expt_no_resp EXCEPTION; l_expt_msg VARCHAR2 (2000);BEGIN --l_tab_user_list := tab_user_list (); IF (l_tab_user_list.COUNT = 0) THEN l_expt_msg := 'no user to change'; RAISE expt_no_user; END IF; --l_arr_resp_list := arr_resp_list (); IF (l_arr_resp_list.COUNT = 0) THEN l_expt_msg := 'no resp to add'; RAISE expt_no_resp; END IF; -- loop user < > FOR idx_tab_user_list IN l_tab_user_list.FIRST .. l_tab_user_list.LAST LOOP DBMS_OUTPUT.put_line ( '>>> ' || idx_tab_user_list || ' , working for user (' || l_tab_user_list (idx_tab_user_list) || ') <<< '); -- check if user exist or active OPEN cur_user (l_tab_user_list (idx_tab_user_list)); FETCH cur_user BULK COLLECT INTO l_tab_user; CLOSE cur_user; IF (l_tab_user.COUNT = 0) THEN DBMS_OUTPUT.put_line ( 'user (' || l_tab_user_list (idx_tab_user_list) || ') is not exist or disabled'); --CONTINUE loop_tab_user_list; goto goto_tab_user_list; END IF; -- loop responsibility < > FOR idx_arr_resp_list IN l_arr_resp_list.FIRST .. l_arr_resp_list.LAST LOOP -- check if responsibility active OPEN cur_resp (l_arr_resp_list (idx_arr_resp_list)); FETCH cur_resp BULK COLLECT INTO l_tab_resp; CLOSE cur_resp; IF (l_tab_resp.COUNT = 0) THEN DBMS_OUTPUT.put_line ( 'resp (' || l_arr_resp_list (idx_arr_resp_list) || ') is not exist or disabled'); --CONTINUE loop_arr_resp_list; goto goto_arr_resp_list; END IF; -- add resp for user DBMS_OUTPUT.put_line ( 'Adding resp (' || l_arr_resp_list (idx_arr_resp_list) || ') for user (' || l_tab_user_list (idx_tab_user_list) || ')'); fnd_user_pkg.addresp ( username => l_tab_user_list (idx_tab_user_list) ,resp_app => l_tab_resp (1).application_short_name ,resp_key => l_tab_resp (1).responsibility_key ,security_group => l_tab_resp (1).security_group_key ,description => NULL ,start_date => TRUNC (SYSDATE) ,end_date => NULL); < > null; END LOOP loop_arr_resp_list; < > null; END LOOP loop_tab_user_list; COMMIT;EXCEPTION WHEN expt_no_user THEN DBMS_OUTPUT.put_line (l_expt_msg); ROLLBACK; WHEN expt_no_resp THEN DBMS_OUTPUT.put_line (l_expt_msg); ROLLBACK;END;/*>>> 1 , working for user (LIAO) <<< Adding resp (system Administrator) for user (LIAO)Adding resp (Purchasing Super User) for user (LIAO)resp (Application Administrator) is not exist or disabled>>> 2 , working for user (KARL) <<< Adding resp (system Administrator) for user (KARL)Adding resp (Purchasing Super User) for user (KARL)resp (Application Administrator) is not exist or disabled>>> 3 , working for user (xx) <<< user (xx) is not exist or disabled*//*1. The Oracle PL/SQL GOTO Statementhttp://psoug.org/definition/GOTO.htm*/ 2. Based on EBS R12.1/Oracle DB 11gR2 DECLARE -- script to add user responsibility for R12.1/11gR2 TYPE tab_user_list IS TABLE OF VARCHAR2 (100); TYPE arr_resp_list IS VARRAY (100) OF VARCHAR2 (100); -- user to be changed l_tab_user_list tab_user_list := tab_user_list ('LIAO' ,'KARL' ,'xx'); -- responsibility to be added l_arr_resp_list arr_resp_list := arr_resp_list ('system Administrator' ,'Purchasing Super User' ,'Application Administrator'); CURSOR cur_user ( pc_username IN VARCHAR2) IS SELECT fu.user_id ,fu.user_name FROM fnd_user fu WHERE fu.user_name = pc_username AND TRUNC (SYSDATE) BETWEEN TRUNC (NVL (fu.start_date, SYSDATE)) AND TRUNC (NVL (fu.end_date, SYSDATE)); TYPE tab_user IS TABLE OF cur_user%ROWTYPE; l_tab_user tab_user; CURSOR cur_resp ( pc_resp IN VARCHAR2) IS SELECT fa.application_id ,fa.application_short_name ,fr.responsibility_id ,fr.responsibility_name ,fr.responsibility_key ,fsg.security_group_key FROM fnd_application fa ,fnd_responsibility_vl fr ,fnd_security_groups fsg WHERE LOWER (fr.responsibility_name) = LOWER (pc_resp) AND fa.application_id = fr.application_id AND fr.data_group_id = fsg.security_group_id; TYPE tab_resp IS TABLE OF cur_resp%ROWTYPE; l_tab_resp tab_resp; expt_no_user EXCEPTION; expt_no_resp EXCEPTION; l_expt_msg VARCHAR2 (2000);BEGIN --l_tab_user_list := tab_user_list (); IF (l_tab_user_list.COUNT = 0) THEN l_expt_msg := 'no user to change'; RAISE expt_no_user; END IF; --l_arr_resp_list := arr_resp_list (); IF (l_arr_resp_list.COUNT = 0) THEN l_expt_msg := 'no resp to add'; RAISE expt_no_resp; END IF; -- loop user < > FOR idx_tab_user_list IN l_tab_user_list.FIRST .. l_tab_user_list.LAST LOOP DBMS_OUTPUT.put_line ( '>>> ' || idx_tab_user_list || ' , working for user (' || l_tab_user_list (idx_tab_user_list) || ') <<< '); -- check if user exist or active OPEN cur_user (l_tab_user_list (idx_tab_user_list)); FETCH cur_user BULK COLLECT INTO l_tab_user; CLOSE cur_user; IF (l_tab_user.COUNT = 0) THEN DBMS_OUTPUT.put_line ( 'user (' || l_tab_user_list (idx_tab_user_list) || ') is not exist or disabled'); CONTINUE loop_tab_user_list; END IF; -- loop responsibility < > FOR idx_arr_resp_list IN l_arr_resp_list.FIRST .. l_arr_resp_list.LAST LOOP -- check if responsibility active OPEN cur_resp (l_arr_resp_list (idx_arr_resp_list)); FETCH cur_resp BULK COLLECT INTO l_tab_resp; CLOSE cur_resp; IF (l_tab_resp.COUNT = 0) THEN DBMS_OUTPUT.put_line ( 'resp (' || l_arr_resp_list (idx_arr_resp_list) || ') is not exist or disabled'); CONTINUE loop_arr_resp_list; END IF; -- add resp for user DBMS_OUTPUT.put_line ( 'Adding resp (' || l_arr_resp_list (idx_arr_resp_list) || ') for user (' || l_tab_user_list (idx_tab_user_list) || ')'); fnd_user_pkg.addresp ( username => l_tab_user_list (idx_tab_user_list) ,resp_app => l_tab_resp (1).application_short_name ,resp_key => l_tab_resp (1).responsibility_key ,security_group => l_tab_resp (1).security_group_key ,description => NULL ,start_date => TRUNC (SYSDATE) ,end_date => NULL); END LOOP loop_arr_resp_list; END LOOP loop_tab_user_list; COMMIT;EXCEPTION WHEN expt_no_user THEN DBMS_OUTPUT.put_line (l_expt_msg); ROLLBACK; WHEN expt_no_resp THEN DBMS_OUTPUT.put_line (l_expt_msg); ROLLBACK;END;/*>>> 1 , working for user (LIAO) <<< Adding resp (system Administrator) for user (LIAO)Adding resp (Purchasing Super User) for user (LIAO)resp (Application Administrator) is not exist or disabled>>> 2 , working for user (KARL) <<< Adding resp (system Administrator) for user (KARL)Adding resp (Purchasing Super User) for user (KARL)resp (Application Administrator) is not exist or disabled>>> 3 , working for user (xx) <<< user (xx) is not exist or disabled*/ 转载地址：http://gjtai.baihongyu.com/