PL/SQL Function returned without value

Question

I wrote below FUNCTION to check given reference number is exist.

FUNCTION find_reference_no(
    p_ref_no IN VARCHAR2) RETURN VARCHAR2
AS

  v_ref_no varchar2(50);

BEGIN

  select REF_NO into v_ref_no from cash where REF_NO = p_ref_no;  
  EXCEPTION
    WHEN no_data_found THEN
    v_ref_no := '#';

RETURN v_ref_no;    

END;

I have called this function in a AFTER INSERT TRIGGER. when I'm inserting data, Ii'm getting error as

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

How can I solve this ?

PS: I'm not inserting data into cash table. I'm inserting data into another table (assume it is table B) and called this function in it's (table B) AFTER INSERT TRIGGER.

This sort of lookup in triggers is precisely the sort of we we should avoid doing in a trigger. It is a hidden performance on every insert, and will harm the scalability of your application. — APC
– APC, Commented Mar 11, 2013 at 9:15
Instead of prefixing your parameter name with p_, consider prefixing the parameter name in the query with the name of the function -- ie. "where cash.ref_no = find_reference_no.ref_no". The v_ is also redundant as a prefix on the variable, which should also be data typed as cash.ref_no%Type rather than varchar2(50). — David Aldridge
– David Aldridge, Commented Mar 11, 2013 at 10:08
The reason the problem is not obvious is that you have misleading code formatting - the RETURN statement looks like it's part of the normal processing for the function, whereas it's actually within the EXCEPTION block for the function. The EXCEPTION keyword should really be at the left-hand margin, same level as the BEGIN and END - for clarity. — Jeffrey Kemp
– Jeffrey Kemp, Commented Mar 12, 2013 at 3:33

APC · Accepted Answer · 2013-03-11 09:13:41Z

13

All functions must execute a RETURN statement. Your function has the RETURN in its exception block, so that statement won't get executed in normal circumstances.

Enclosing select statement by additional begin end block with its own exception section will solve your problem. So your function might look like this:

create or replace function find_reference_no(
    p_ref_no IN VARCHAR2) return varchar2
AS
  v_ref_no varchar2(50);
begin
  begin
    select REF_NO 
       into v_ref_no 
       from cash 
      where REF_NO = p_ref_no;  
  exception
     WHEN no_data_found THEN
          v_ref_no := '#';
   end;
  return v_ref_no;    
end;

edited Mar 11, 2013 at 9:13

APC

147k20 gold badges180 silver badges289 bronze badges

answered Mar 11, 2013 at 9:06

Nick Krasnov

27.3k6 gold badges66 silver badges81 bronze badges

Sign up to request clarification or add additional context in comments.

Comments

David Aldridge · Accepted Answer · 2013-03-11 10:13:27Z

Since the function just returns the same value as the parameter if it exists in the table, you could avoid the awkward use of exception handling and rewrite this as:

  function find_reference_no(
      ref_no in varchar2)
  return varchar2
  as
    row_count integer
  begin
    select count(*)
    into   row_count
    from   cash
    where  cash.ref_no = find_reference_no.ref_no and
           rownum      = 1

    if row_count = 0
      return '#'
    else
      return ref_no
    end if;

  end find_reference_no;

I'd return a 1 or 0 (ie. the value of row_count) to indicate that the record does or does not exist though.

  function find_reference_no(
      ref_no in varchar2)
  return varchar2
  as
    row_count integer
  begin
    select count(*)
    into   row_count
    from   cash
    where  cash.ref_no = find_reference_no.ref_no and
           rownum      = 1

    return row_count

  end find_reference_no;

Collectives™ on Stack Overflow

PL/SQL Function returned without value

2 Answers 2

Comments

Comments

Your Answer

Hot Network Questions

Collectives™ on Stack Overflow

2 Answers 2

Comments

Comments

Your Answer

Sign up or log in

Post as a guest

Related