Ошибка ora 06503

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.

If you want to check correctness of a datetime information provided as a character literal, it seems that way, you can get, besides ORA-01858, plenty of other exceptions, depending on incorrectness of datetime value or/and format. You can, of course, write as many IF conditions, or better yet, associate an exception name with an exception number by using exception_init() pragma, as many exceptions can be potentially raised, or you can declare a one single exception, say, wrong_date_time and catch it, as follows:

create or replace package pkg1 as
  function date_chk(p_value in varchar2, p_format in varchar2) return number;
end;

create or replace package body pkg1 as
  -- This function is simply trying to convert character literal to a value of 
  -- date data type. If it's not possible for whatever reason it raises 
  -- user defined ORA-20001 exception. We will catch it later.
  function to_date1( p_value in varchar2, p_format in varchar2) return date is
  begin
    -- Here we add fx modifier to require exact matching between
    -- character literal and format model, otherwise
    -- something like 21.01 will give us 1, which is not quite correct.
    return to_date(p_value, concat('fx',p_format)); 
  exception
    when others then raise_application_error(-20001, 'wrong date');
  end;

  function date_chk(p_value in varchar2, p_format in varchar2) return number is
    wrong_date_time exception;
    pragma exception_init(wrong_date_time, -20001);
    l_res date;
  begin
    l_res := pkg1.to_date1(p_value, p_format);
    return 1;
  exception
    when wrong_date_time then
      return 0;
  end;
end;

Test case:

SQL> select pkg1.date_chk('09:99', 'hh24:mi') as res
  2    from dual
  3  ;
       RES
----------
         0

SQL> select pkg1.date_chk('09:59', 'hh24:mi') as res
  2    from dual
  3  ;
       RES
----------
         1

SQL> select pkg1.date_chk('asdas', 'hh24:mi') as res
  2    from dual
  3  ;
       RES
----------
         0

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.