ORA 06503 Function returned without value

Question

I write a function which returned with values in database it have a value but returned this error

ORA 06503 Function returned without value

My function is below and data is also in table

FUNCTION Leaves_attFormula RETURN CHAR IS
B VARCHAR2(40);
C VARCHAR2(40);

BEGIN
  
     SELECT ATT_PRESENT INTO C FROM PAY_IN_OUT
     WHERE   ATT_DATE= :ATT_DATE
     AND ATT_DATE BETWEEN :DATE1 AND :DATE2
     AND EMP_CODE = :EMP_ID;

     IF C = 'L' THEN
        B := 'LEAVE';
        RETURN B;
     END IF;

EXCEPTION
WHEN NO_DATA_FOUND THEN
   RETURN NULL;
END;

@MaheswaranRavisankar Thanks for your attention but no changes `C <> 'L'` any other idea — aws, Mar 13 '21 at 11:20
@aws - what do you mean "no changes"? Maheswaran gave you the answer to your question. If `C <> 'L'` (and also when `C` is `null`) your function never sees a `return` statement, much less return a value. If `C = 'L'` you want to return `'LEAVE'`, OK; what should the function return in all other cases? — , Mar 13 '21 at 11:32
@mathguy in other all cases its also throw me same error if i did `c <> 'L'` it throw me same error as on = — aws, Mar 13 '21 at 11:44
What part of my question do you not understand? Forget the code you wrote so far, and forget the error. If you were to start over, **what is the function supposed to do**? From what you wrote so far, we know what it must return if C='L'. The value of C is based on data from a table. OK, if C is **not** 'L' after you check the table, what should the function return? This has nothing to do with your code, or with any error. — , Mar 13 '21 at 11:46
@mathguy sorry first i didn't understand you are else statement to return other what happen thanks for pointing and taunting me you taunt did work I am posting answer now thanks dear for your concern and attention I am posting answer — aws, Mar 13 '21 at 11:51

score 0 · Answer 1 · answered Mar 13 '21 at 11:52

0

the answer is

function Leaves_attFormula return Char is
B varchar2(40);
C VARCHAR2(40);
begin  
    select ATT_PRESENT INTO C FROM PAY_IN_OUT
  WHERE   EMP_CODE = :EMP_ID   
   AND ATT_DATE = :ATT_DATE
   AND ATT_DATE BETWEEN :DATE1 AND :DATE2;
     IF C = 'L'  THEN
        B := 'LEAVE';
    return B;
    else return :CF_WEEKREST;
    END IF;
    EXCEPTION
    WHEN NO_DATA_FOUND THEN
  RETURN NULL;
    end;

answered Mar 13 '21 at 11:52

aws

83
6

1

I suggest you have a look at [code indentation](https://en.wikipedia.org/wiki/Indentation_style). There is a reason it's widely used in all programming languages. – William Robertson Mar 13 '21 at 15:00
Also, the function should probably return a `varchar2`, not a `char`. 99% of the time, [char is a bug](https://stackoverflow.com/a/42165653/230471). And it doesn't seem like good program design for the function to return `:CF_WEEKREST` which is not part of the function but presumably some field value in the report. It might be neater if all the relevant values were passed into the function at the start. – William Robertson Mar 13 '21 at 15:04

ORA 06503 Function returned without value

1 Answers1