Fun with copy-and-paste code

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”.

Jeffrey Kemp 5 May 2006

code-snippet-of-the-day / PL/SQL / what-not-to-do /

About Jeffrey Kemp

Application Designer & Developer at Oracle specialising in Oracle APEX (Application Express), Oracle SQL and PL/SQL. Oracle ACE Alumni ♠️. Piano player, father, husband, Christian. The views expressed on this blog are my own and do not necessarily reflect the views of Oracle.