CODEWITHSUNDEEP
sqldatabaseoracle-databaseselectplsql
Dilshad Abduwali
Dilshad Abduwali

Reputation: 1458

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

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.

Upvotes: 5

Views: 50899

Answers (2)

Rachcha
Rachcha

Reputation: 8816

Try 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
        EXIT WHEN c1%notfound;
        IF upper(r1.locality) = upper(p_suburb) THEN 
          v_postcode := r1.postcode;
          EXIT;
        END IF;
    END LOOP;
    return v_postcode;
    exception WHEN others then
    return null;  
END; 
/

By exiting the FOR loop on finding out the first post code you are ensuring that there is not a TOO_MANY_ROWS exception raised. Also, by putting the return outside the IF statement you are ensuring that atleast a NULL is returned in case nothing is found.

Upvotes: 0

hol
hol

Reputation: 8423

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

Upvotes: 9

Related Questions