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;

What's the full ORA code error, and did you get it during Compile time, or runtime ? — Ditto
– Ditto, Commented Aug 23, 2016 at 19:04
ORA-06503: PL/SQL: Function returned without value ORA-06512: at "ESAPP.EXPORT", line 24 ORA-06512: at "ESAPP.EXPORT", line 426 ORA-06512: at line 1 <br> The error is thrown at runtime when a shell script calling a SP which called this function. Line 24 is the declaration of the SP and line 426 is the function call. — elsiehsu
– elsiehsu, Commented Aug 24, 2016 at 0:45
Your function has an execution path that doesn't end to a return statement. It's simply a bug in the function implementation. Unfortunately the compiler won't catch the bug for your (at least in 11gR2) but the exception takes place in runtime only. — user272735
– user272735, Commented Aug 24, 2016 at 6:25

Ditto · Accepted Answer · 2016-08-23 19:17:01Z

3

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.

answered Aug 23, 2016 at 19:17

Ditto

3,3693 gold badges16 silver badges31 bronze badges

Sign up to request clarification or add additional context in comments.

1 Comment

elsiehsu Over a year ago

Oh yes. I forgot the return part here and expecting the recursive call return it .... Thx a lot!

Bob Jarvis - Слава Україні · Accepted Answer · 2016-08-23 19:50:49Z

0

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.

answered Aug 23, 2016 at 19:50

Bob Jarvis - Слава Україні

50.2k10 gold badges81 silver badges119 bronze badges

1 Comment

elsiehsu Over a year ago

Thanks. I am actually calling this function in a loop within a SP and doesn't want to exit when there is an exception.

Collectives™ on Stack Overflow

PL/SQL recursive function return no value

2 Answers 2

1 Comment

1 Comment

Your Answer

Hot Network Questions

Collectives™ on Stack Overflow

2 Answers 2

1 Comment

1 Comment

Your Answer

Sign up or log in

Post as a guest

Related