Workflow AdHoc Role Issue

I was creating a ad-hoc role for list of users. Somehow this list of users got blanks between and the WF core routine "wf_directory.createadhocrole" would not complete. While parsing the string for users, it goes to infinite loop. So had to parse the user list to make sure, no blanks are passed.

For example, if the users list is 'A,,B,C,D,,', the routine would hang.

Put together a routine to remove extra commas and used this routine to make the user string good to go..

SET SERVEROUT ON
SET TIME ON

DECLARE

TYPE VARCHAR2_TABLE IS TABLE OF VARCHAR2(32767) INDEX BY BINARY_INTEGER;
lv_users_tab         VARCHAR2_TABLE;
lv_users_pruned_list VARCHAR2(4000);
ln_tablen            INTEGER;

PROCEDURE delimstring_to_table
( p_delimstring IN  VARCHAR2
 ,p_table       OUT VARCHAR2_TABLE
 ,p_nfields     OUT INTEGER
 ,p_delim       IN  VARCHAR2 DEFAULT ','
)
IS
 v_string   VARCHAR2(32767) := p_delimstring;
 v_nfields  PLS_INTEGER := 0;
 v_table    varchar2_table;
 v_delimpos PLS_INTEGER := INSTR(p_delimstring, p_delim);
 v_delimlen PLS_INTEGER := LENGTH(p_delim);
BEGIN

 DBMS_OUTPUT.PUT_LINE('==================START OF DELIMS==========');
 WHILE v_delimpos > 0
  LOOP
   IF TRIM(SUBSTR(v_string,1,v_delimpos-1)) IS NOT NULL THEN
    v_nfields := v_nfields+1;
    v_table(v_nfields) := SUBSTR(v_string,1,v_delimpos-1);
    DBMS_OUTPUT.PUT_LINE('v_table(v_nfields) ' || v_table(v_nfields));
    DBMS_OUTPUT.PUT_LINE('v_string  ' || v_string );
    DBMS_OUTPUT.PUT_LINE('v_nfields  ' || v_nfields );
   END IF;
   v_string := SUBSTR(v_string,v_delimpos+v_delimlen);
   v_delimpos := INSTR(v_string, p_delim);
  END LOOP;
 IF TRIM(v_string) IS NOT NULL THEN
  v_table(v_nfields) := v_string;
 END IF;
 p_table := v_table;
 p_nfields := v_nfields;
 DBMS_OUTPUT.PUT_LINE('==================END OF DELIMS==========');
END delimstring_to_table;

BEGIN
 DBMS_OUTPUT.ENABLE(20000000);

 ln_tablen            := NULL;
 lv_users_pruned_list := ',, ,, USERA,, ,,,,, USERB, USERC,D,,,,';

 DBMS_OUTPUT.PUT_LINE('ORIGINAL LIST ' || lv_users_pruned_list);
 delimstring_to_table ( p_delimstring   => lv_users_pruned_list
                       ,p_table         => lv_users_tab
                       ,p_nfields       => ln_tablen
                       ,p_delim         => ',');
 lv_users_pruned_list := NULL;
 DBMS_OUTPUT.PUT_LINE('ln_tablen ' || ln_tablen);
 FOR i IN 1 .. ln_tablen LOOP
  DBMS_OUTPUT.PUT_LINE(' COUNTER ' || I);
  DBMS_OUTPUT.PUT_LINE(' lv_users_tab(i) ' || lv_users_tab(i));
  IF lv_users_pruned_list IS NULL THEN
   lv_users_pruned_list := lv_users_tab(i);
  ELSE
   lv_users_pruned_list := lv_users_pruned_list ||','|| lv_users_tab(i);
  END IF;
 END LOOP;
 DBMS_OUTPUT.PUT_LINE('PRUNED LIST ' || lv_users_pruned_list);
END;
/