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!

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

324 views

SQL & PL/SQL

function returned without value

Comments

Post Details