在一些比较注重权限的EBS项目中, 普通用户通常没有System Administrator权限, 只能由DBA给一个一个用户一个一个职责加, 纯手工的一段oracle ebs中给指定用户增加指定职责的plsql脚本[日期：2012-11-23]来源：Linux社区作者：t0nsha[字体：]在一些比较注重权限的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 Statement*/，