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

在一些比较注重权限的EBS项目中, 普通用户通常没有System Administrator权限, 只能由DBA给一个一个用户一个一个职责加, 纯手工的话很麻烦, 于是写了一段PLSQL脚本, 留着备用. 由于Oracle 10g尚不支持continue语句, 因此10g和11g分开写了.1. Based on EBS R12.0.6/Oracle DB 10gR2DECLARE
-- 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
<<loop_tab_user_list>>
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
<<loop_arr_resp_list>>
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）;
<<goto_arr_resp_list>> null;
END LOOP loop_arr_resp_list;
<<goto_tab_user_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
http://psoug.org/definition/GOTO.htm
*/

Oracle - 利用自带函数进行16进制与字符互转EBS R12.1.1 on Oracle Linux 64 5.7??装过程中碰到的两个问题相关资讯 Oracle EBS plsql

PLSQL 经常自动断开失去连接的解决（09/19/2015 15:57:43）
PLSQL Developer 不能连接 64位（03/30/2015 15:56:21）
PLSQL连接远程数据库（12/12/2014 16:53:57）

Oracleclient+PLSQL Developer实现（05/09/2015 10:55:10）
64位Oracle客户端上PLSQL无法识别（03/30/2015 15:22:13）
登录Oracle EBS系统遭遇Tomcat页面（06/06/2014 19:49:49）

本文评论查看全部评论（0）