一段Oracle EBS中给指定用户增加指定职责的PLSQL脚本

在一些比较注重权限的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

*/

，