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

An important thing regarding function, you would agree with me that, at least once a PL/SQL developer must have heard that "A function MUST ALWAYS RETURN a VALUE of proper datatype". Having been said that a million times on various platforms, still developers make this mistake.

ORA-06503: 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.

DB version : 11.2.0.2.0

Let's see the various scenarios of this error :

Without a RETURN statement in the function body and without exception handler(most stupid way):

SQL> set serveroutput on;

SQL> CREATE OR REPLACE FUNCTION f_test(i_val NUMBER)
2 RETURN NUMBER AS
3    o_val NUMBER;
4 BEGIN
5    SELECT 100 / i_val
6    INTO o_val
7    FROM DUAL;
8 END;
9 /
Function created

SQL> select f_test(100) from dual;
select f_test(100) from dual
ORA-06503: PL/SQL: Function returned without value
ORA-06512: at "F_TEST", line 8

Now, in the above code, the mathematical logic was correct, hence there was no SQL error to override the PL/SQL error. Let's see how ORA-01476 will override the ORA-06503 error.

SQL> CREATE OR REPLACE FUNCTION f_test(i_val NUMBER)
2 RETURN NUMBER AS
3    o_val NUMBER;
4 BEGIN
5    SELECT 100 / i_val
6    INTO o_val
7    FROM DUAL;
8 END;
9 /
Function created

SQL> select f_test(0) from dual;
select f_test(0) from dual
ORA-01476: divisor is equal to zero
ORA-06512: at "F_TEST", line 5

Well, that's quite obvious, isn't it?

2. Without a RETURN statement in the exception handler(most common mistake) :

SQL> CREATE OR REPLACE FUNCTION f_test(i_val NUMBER)
2 RETURN NUMBER AS
3    o_val NUMBER;
4 BEGIN
5    SELECT 100 / i_val
6    INTO o_val
7    FROM DUAL;
8
9    RETURN o_val;
10
11 EXCEPTION
12    WHEN OTHERS THEN
13    NULL;
14 END;
15 /
Function created

SQL> select f_test(0) from dual;
select f_test(0) from dual
ORA-06503: PL/SQL: Function returned without value
ORA-06512: at "F_TEST", line 14

----------------------------------------/ OFF TOPIC /----------------------------------------

This is somewhat important to share.

EXCEPTION WHEN OTHERS THEN NULL;
--> is itself a bug in the code waiting for its chance to break the code.

At least a good developer would remember that WHEN OTHERS should be always followed by a RAISE. Re-raising the error would show us the root cause, rather than the confusing "ORA-06503: PL/SQL: Function returned without value" error.

SQL> CREATE OR REPLACE FUNCTION f_test(i_val NUMBER)
2 RETURN NUMBER AS
3    o_val NUMBER;
4 BEGIN
5    SELECT 100 / i_val
6    INTO o_val
7    FROM DUAL;
8
9 RETURN o_val;
10
11 EXCEPTION
12    WHEN OTHERS THEN
13    NULL;
14    RAISE;
15 END;
16 /
Function created

SQL> select f_test(0) from dual;
select f_test(0) from dual
ORA-01476: divisor is equal to zero
ORA-06512: at "F_TEST", line 14

----------------------------------------/ OFF TOPIC /----------------------------------------

Now let's put a RETURN statement at required places and the code should work fine without any error :

SQL> CREATE OR REPLACE FUNCTION f_test(i_val NUMBER)
2 RETURN NUMBER AS
3    o_val NUMBER;
4 BEGIN
5    SELECT 100 / i_val
6    INTO o_val
7    FROM DUAL;
8
9    RETURN o_val;
10
11 EXCEPTION
12    WHEN OTHERS THEN
13    DBMS_OUTPUT.PUT_LINE('Came inside Exception handler');
14    RETURN 0;
15 END;
16 /
Function created

SQL> select f_test(0) from dual;
F_TEST(0)
----------
0
Came inside Exception handler

Bottom line is that :

• A function MUST ALWAYS RETURN a value of proper datatype, no matter from the body or exception.
• We must do something with the error not just return junk. We must RAISE/log error and handle it, do something about the error so that underlying process has no impact.
• Lastly, not to forget, EXCEPTION WHEN OTHERS THEN NULL; --> is itself a bug in the code waiting for its chance to break the code.