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

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