PL/SQL recursive function return no value

Question

I got the error message while running Oracle PL/SQL recursive function

function returned without value

Anyone knows what might be the issue?

Here's my function

FUNCTION cgic (cnt IN NUMBER)
   RETURN VARCHAR2
   IS
      n_inv_code   VARCHAR2 (20);
      t_ic_chk                           NUMBER;
   BEGIN
                  SELECT DBMS_RANDOM.STRING ('X', 10)
      INTO n_inv_code
      FROM DUAL;

                  select count(*) into t_ic_chk from inv_code where inv_code = n_inv_code and rownum = 1;

      IF t_ic_chk = 1
      THEN
                    n_inv_code := cgic(cnt);
      ELSE
                    IF t_ic_chk = 0
                                THEN
                      RETURN n_inv_code;
                                END IF;
      END IF;
   END cgic;

Answer 1

The code starting with IF t_ic_chk = 1 might be replaced with

CASE t_ic_chk
  WHEN 0 THEN RETURN n_inv_code;
  WHEN 1 THEN RETURN cgic(cnt);
  ELSE RAISE_APPLICATION_ERROR(-20202, 'Unexpected t_ic_chk value=' || t_ic_chk);
END;

Done this way your function will either return an expected value or will raise an exception if it doesn't know what to do with the value it finds in t_ic_chk.

I do wonder why you're passing in the cnt parameter as it's never used in the procedure, except to pass it on in the recursive call.

Best of luck.

Answer 2

In the event t_ic_chk = 1

you assign the value of the recursive function back to the variable: n_inv_code

however, you don't DO anything with it. You probably want to return it.

I would recommend this code in your final section:

  IF t_ic_chk = 1
  THEN
                n_inv_code := cgic(cnt);
  END IF;
  RETURN n_inv_code;
END cgic;

That's all you need:

1) if you find a row, recurse back in until you can't find one, and return that value. 2) if you can't find a row, return that value back. 3) in the event you found a row, just hand-shake the value returned back to whoever called you.