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

Today is the day of tongue-in-cheek errors.
I mean, it takes 5 seconds for an experienced programmer to spot the problem, but still i think there could be someone out there that might find this useful information.

Say you have a function returning a value as result of a query and after doing some tests you realize that there can be a situation where it throws a no data found error because the condition in the WHERE clause is not met.

Then you decide to trap this error and return zero as follows:

CREATE PACKAGE test
as
function test_fn return integer;
end;
/
CREATE PACKAGE BODY test
as
function test_fn
return integer
is      
 n  integer;
begin   
 select 123
   into n
   from dual
  where 1=0; -- i'm forcing the failure of WHERE

 return n;
exception
when no_data_found then
 n := 0;
end test_fn;
end test;
/
select test.test_fn from dual;

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

As you can see, i forgot to add something in the error trapping block:

  ...
when no_data_found then
 n := 0;
 return n; -- this is necessary!
end test_fn;
end test;

Note that in this case i could have written directly

return 0;

but in real applications sometimes one wants to perform additional things, like retrieving another value from a different table or from another procedure, so one could actually need a variable to store the intermediate result prior to returning the value to the caller.

Then you forget to add one more return statement and bang, ORA-06503!
;-)

See message translations for ORA-06503 and search additional resources.

---

ORA-06503: PL/SQL: funzione restituita senza valore
ORA-06503: PL/SQL: La función no ha devuelto ningún valor
ORA-06503: PL/SQL: La funció no retorna cap valor
ORA-06503: PL/SQL : La fonction ne ramène aucune valeur
ORA-06503: PL/SQL: Funktion hat keinen Wert zurückgegeben
ORA-06503: PL/SQL: Επιστροφή από συνάρτηση χωρίς τιμή
ORA-06503: PL/SQL: Funktion returnerede uden værdi
ORA-06503: PL/SQL: funktion returnerade utan värde
ORA-06503: PL/SQL: Funksjonen returnerte uten verdi
ORA-06503: PL/SQL: Toiminto on palautunut ilman arvoa
ORA-06503: PL/SQL: A függvény érték nélkül tért vissza
ORA-06503: PL/SQL: Funcţia nu a returnat nici o valoare
ORA-06503: PL/SQL: functie teruggegeven zonder waarde.
ORA-06503: PL/SQL: Função retornada sem valor
ORA-06503: PL/SQL: Função devolvida sem valor
ORA-06503: PL/SQL: Возврат функции без значения
ORA-06503: PL/SQL: funkce nevrátila hodnotu
ORA-06503: PL/SQL: Návrat funkcie bez hodnoty
ORA-06503: PL/SQL: Powrót z funkcji bez wartości
ORA-06503: PL/SQL: Fonksiyon değer döndürmedi