PL/SQL Function returns ORA-06503: PL/SQL: Function returned without value

Question

I have to write a pl/sql code(actually a function) which returns a postcode when by taking the suburb as its parameter the code is like this:

create or replace 
FUNCTION get_postCode(p_suburb IN varchar2)
RETURN varchar2 
IS
--
v_postcode varchar2;
--
CURSOR c1 IS 
SELECT locality, postcode FROM table_postcode;
--
BEGIN
--
FOR r1 IN c1 
loop
    IF upper(r1.locality) = upper(p_suburb)
    THEN 
      v_postcode := r1.postcode;
      return v_postcode;
    END IF;
exit WHEN c1%notfound;
END loop;
-- return v_postcode;
--
exception WHEN others then
v_postcode := null;  
END; 

the table_postcode was obtained from Post Office an it contains the suburbs(locality as column in the talble) and postcode and other fields that are not relevant to this case.

when I use the function it is return correct value and when I use this function as on of the columns of the SELECT clause it return ONLY if I don't add any further clauses after FROM clause. It is quit strange for me.

the situation is :

select street, suburb, get_postcode(suburb) from my_table;

the line above gives me the result but

select street, subur, get_postcode(suburb) from my_table order by suburb;

fails and gives me the following error message:

ORA-06503: PL/SQL: Function returned without value
ORA-06512: at "U11254683.GET_POSTCODE", line 25
06503. 00000 -  "PL/SQL: Function returned without value"
*Cause:    A call to PL/SQL function completed, but no RETURN statement was
       executed.
*Action:   Rewrite PL/SQL function, making sure that it always returns
       a value of a proper type.

if I call the function in a block like:

Declare
v_post varchar2(10);
Begin
v_post := get_postcode('Sydney');
DBMS_OUTPUT.PUT_LINE('The post code is '||v_post);
End;

result is correct and gives me 2000.

Solution

You should return something when nothing is found. Before exception comment in the statement

return v_postcode;

The way your routine is written it may does find nothing and then hits the end of the function without returning anything, hence the error "function returned without value". It is literally exactly this.

However, you can do this much easier by directly selecting on the suburb. But what would you do if you get more than one postcode to suburb (if this is possible)?

create or replace FUNCTION get_postCode(i_suburb IN varchar2)
RETURN varchar2 
IS
  l_postcode varchar2;
BEGIN
  select postcode 
    into l_postcode
   where upper(i_suburb) = upper(locality);
-- 
  return l_postcode;
exception 
  WHEN others then
    return null;  
END; 

In this version you would get null if one suburb exists in more than one postcode. It is now up to your design decision how to handle that situation. You could then handle it in the exception

exception
   when TOO_MANY_ROWS then
        return '**Error** Several Values'; -- do something meaningful here 
   when NO_DATA_FOUND then
        return null; -- if you want to return null on nothing found

Comments

[Dilshad Abduwali]

THANKS FOR YOUR ADVICE. I HAVE TRIED YOUR CODE, AND INDEED I HAD THE ISSUE WITH THE TOO MANY ROWS ERROR WHICH IS NOW BEING HANDLED IN EXCEPTION

[a_horse_with_no_name]

@DilshatAbduwalli: STOP SHOUTING AT US

[hol]

@a_horse_with_no_name I thought the same, but not everybody knows that uppercase means shouting. In this case I interpreted it not as shouting. Actually is there a Stackoverflow rule that uppercase means shouting or is this from the general web ethnic?

[hol]

In your code it still does not work, because after the exception it will not return to the code (where it got the exception) and still no return statement is hit.

[Rachcha]

Well, a similar code works perfectly for me on my machine! :D

[hol]

I meant if any exception happens (more unlikely how it is coded now) it will go to the exception and then does not return anything.

[Rachcha]

@hol: Yes, you're right. I should put a return null in the exception block. Editing.

[Rachcha]

Though, I believe it is less likely that I will get a NO_DATA_FOUND or TOO_MANY_ROWS exception now.

[a_horse_with_no_name]

@hol: uppercase is considered shouting in any "online" conversation (forum, mails, newsgroups, ...) as far as I know.

[hol]

@a_horse_with_no_name Also as far as I know, but more than once I observed that this is no (more) common knowledge :-) And since then I interprete it good or bad depending on the context.

[Admin]

Wow, that's perfect answer