title: PL/SQL Function returns ORA-06503: PL/SQL: Function returned without value
body: <p>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:</p>
  
  <pre><code>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; 
  </code></pre>
  
  <p>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.</p>
  
  <p>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.</p>
  
  <p>the situation is :</p>
  
  <pre><code>select street, suburb, get_postcode(suburb) from my_table;
  </code></pre>
  
  <p>the line above gives me the result  but </p>
  
  <pre><code>select street, subur, get_postcode(suburb) from my_table order by suburb;
  </code></pre>
  
  <p>fails and gives me the following error message:</p>
  
  <pre><code>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.
  </code></pre>
  
  <p>if I call the function in a block like:</p>
  
  <pre><code>Declare
  v_post varchar2(10);
  Begin
  v_post := get_postcode('Sydney');
  DBMS_OUTPUT.PUT_LINE('The post code is '||v_post);
  End;
  </code></pre>
  
  <p>result is correct and gives me 2000.</p>