Tuesday, May 22, 2007

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

4 comments:

FTSI said...

Thanks - I got the same error and got it fixed.

Kat said...

Thank you very much, this was very helpful.

Anonymous said...

great work! It helped :). Just one thing: add note that return for main part of procedure should be before "cathing exception block" :)

Anonymous said...

even in 2013 I used the information presented in this article :)

yes you can!

Two great ways to help us out with a minimal effort. Click on the Google Plus +1 button above or...
We appreciate your support!

latest articles