一段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) 
  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 
*/