Came across this in a form (6i) to be run on a 9i db. Not only is this code about 33 lines of code too long and issues any number of unnecessary database queries, its name is quite unrelated to its intended function. Needless to say it was easily replaced with a single call to INSTR.
PROCEDURE alpha_check (ref_in IN VARCHAR2 ,ref_out OUT VARCHAR2) IS -- Procedure included to distinguish -- ref_in between ID or reference. l_alpha_char VARCHAR2 (1); l_alpha_pos NUMBER; l_found_pos NUMBER; l_search_string VARCHAR2 (100) := ' '; CURSOR cur_get_next_alpha(N NUMBER) IS SELECT SUBSTR(l_search_string,N,1) FROM dual; CURSOR cur_check_for_alpha(C VARCHAR2)IS SELECT INSTRB(ref_in,C, 1) FROM dual; BEGIN IF ref_in IS NULL THEN ref_out := 'X'; RETURN; END IF; FOR I IN 1..LENGTH(l_search_string) LOOP OPEN cur_get_next_alpha(I); FETCH cur_get_next_alpha INTO l_alpha_char; CLOSE cur_get_next_alpha; FOR J IN 1..LENGTH(ref_in) LOOP OPEN cur_check_for_alpha(l_alpha_char); FETCH cur_check_for_alpha INTO l_found_pos; CLOSE cur_check_for_alpha; IF l_found_pos > 0 THEN ref_out := 'N'; RETURN; END IF; END LOOP; END LOOP; ref_out := 'Y'; EXCEPTION WHEN OTHERS THEN pc_ref_out := 'X'; END;
Looks like it may have been copied from the same source as “As bad as it gets”.