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;
/