SQL & PL/SQL

Announcement

For appeals, questions and feedback about Oracle Forums, please email oracle-forums-moderators_us@oracle.com. Technical questions should be asked in the appropriate category. Thank you!

Interested in getting your voice heard by members of the Developer Marketing team at Oracle? Check out this post for AppDev or this post for AI focus group information.

function returned without value

351480Dec 3 2004 — edited Dec 7 2004

I have a function that compiles fine, but I can not return the value. I haven't created many functions and I am unsure if its just a simple syntax error, or if my whole structure is wrong.

CREATE OR REPLACE FUNCTION f_hours (v_id varchar2, v_term varchar2) RETURN varchar2 IS
Begin
Declare
v_hours number(5) := 0 ;
CURSOR c_hours is
select shrtgpa_hours_earned
from shrtgpa, spriden
where shrtgpa_pidm = spriden_pidm
and spriden_change_ind is null
and spriden_id = 'v_id'
and exists (select 'x' from sfrstcr
where sfrstcr_pidm = spriden_pidm
and sfrstcr_rsts_code='RE'
and sfrstcr_camp_code = '1'
and sfrstcr_term_code = 'v_term');
Begin
open c_hours;
loop
fetch c_hours into v_hours;
exit when c_hours%notfound;
if v_hours >= 48 then
return 'TRUE';
else
return 'FALSE';
end if;
end loop;
close c_hours;
EXCEPTION
when no_data_found then
return ('wrong id');
end;
end;

========
*
ERROR at line 1:
ORA-06503: PL/SQL: Function returned without value
ORA-06512: at "F_HOURS", line 32
ORA-06512: at line 1

23650

I would suggest rewriting your function to look something like:

create or replace function f_hours 
  (p_id    in  spriden.spriden_id%type, 
   p_term  in  sfrstcr.sfrstcr_term_code%type) 
  return varchar2 
is
  v_result  varchar2(8);
begin
  select case when shrtgpa_hours_earned >= 48 then 'TRUE'
              else 'FALSE'
         end
    into v_result     
    from shrtgpa, spriden
   where shrtgpa_pidm = spriden_pidm
     and spriden_change_ind is null
     and spriden_id = p_id
     and exists (select 'x' 
                   from sfrstcr
                  where sfrstcr_pidm = spriden_pidm
                    and sfrstcr_rsts_code= 'RE'
                    and sfrstcr_camp_code = '1'
                    and sfrstcr_term_code = p_term);

  return (v_result);
exception
  when no_data_found then
    return ('wrong id');
end;

1) TYPE those input parameters after the columns they represent.
2) Use an implicit cursor to fetch the row.
3) Convert the hours to the TRUE/FALSE value on the fly.
4) You had single quotes around the parameter names. The function was looking for the literal values 'v_id' and 'v_term'. I would suggest a 'p_' prefix for the parameter names.

APC

Todd's answer is fine but doesn't explain the exact cause of the error:

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

...means that the program completed without coming across a RETURN statement. We can see in your code that this will happen whenever the query returns no rows, because under those circumstances this line...

exit when c_hours%notfound;

...means this code...

if v_hours >= 48 then

never gets exectuted.

Todd's solution works around this by having an exception handler with a RETURN clause. Personally I prefer having functions with only one RETURN point, which usually means nested blocks populating a variable called return_value.

Cheers, APC

351480

Thanks for your help, but I'm still having trouble with this. I get an error from the case statement. PLS-00103: Encountered the symbol "CASE" when expecting one of the following: etc

I am using sqlPlus 8.0.6 Does that version allow for case statements?

23650

CASE is not supported until 8.1.6 I believe. Here is the equivalent DECODE expression:

sql>select hours,
  2         case when hours >= 48 then 'TRUE'
  3              else 'FALSE'
  4         end case_version,
  5         decode(sign(hours - 48), 1, 'TRUE', 0, 'TRUE', 'FALSE') decode_version
  6    from t;

    HOURS CASE_ DECOD
--------- ----- -----
       40 FALSE FALSE
       45 FALSE FALSE
       46 FALSE FALSE
       47 FALSE FALSE
       48 TRUE  TRUE
       49 TRUE  TRUE
       50 TRUE  TRUE
       60 TRUE  TRUE

23650

Thanks Andrew. I think I got distracted by all the other issues I saw with the code and failed to answer the original question.

As far as single returns, I will always have only one return within the main block, but I personally don't mind returns coming from the outer exception block. Trying to maintain a single return is often too much nesting for my taste.

1 - 5

Locked Post

New comments cannot be posted to this locked post.

Locked on Jan 4 2005

Added on Dec 3 2004

5 comments

198 views

SQL & PL/SQL

function returned without value

Comments

Post Details