Checks day where week rest than insert week rest else present but get only exception

Question

Checks day where week rest than insert week rest else present but get only exception where i am doing wrong please it takes data from employees tables where week rest with 1,2.. then i convert with days names and compare to my date which is :ATT_DATE what i can do for this

function CF_WEEKRESTFormula return CHAR is
v_rest     number;
v_rest_day varchar2(3);
A VARCHAR2(40);
B VARCHAR2(40);
begin
  select week_rest into v_rest from EMPLOYEES
  where employee_id1=:employee_id1
  and :att_date between :date1 and :date2;
  if v_rest=1 then
    v_rest_day:='SUNDAY';
  elsif v_rest=2 then
    v_rest_day:='MONDAY';
  elsif v_rest=3 then
    v_rest_day:='TUESDAY';
  elsif v_rest=4 then
    v_rest_day:='WEDNESDAY';
  elsif v_rest=5 then
    v_rest_day:='THURSDAY';
  elsif v_rest=6 then
    v_rest_day:='FRIDAY';
  elsif v_rest=7 then
    v_rest_day:='SATURDAY';
  end if;
  select RTRIM(to_char(:ATT_DATE,'DAY')) into a from dual;
    IF A = v_rest_daY  
        THEN B := 'WEEK REST';
    ELSE B := 'PRESENT';
        END IF;
    return B;
 -- return v_rest_day;
exception
  when others then
    return 'AAA';
end;

it just return exception

Answer 1

Never catch the OTHERS exception. It hides all the error messages from you and prevents you from being able to easily debug issues.

You appear to be converting everything to strings when it would be much easier to leave it all as numbers. Without a minimal representative example, it is difficult to check the code but you appear to want:

FUNCTION CF_WEEKRESTFormula
RETURN CHAR
IS
  v_rest     number;
BEGIN
  SELECT week_rest
  INTO   v_rest
  FROM   EMPLOYEES
  WHERE  employee_id1=:employee_id1
  AND    :ATT_DATE between :DATE1 and :DATE2;

  IF TO_CHAR( :ATT_DATE, 'D' ) = v_rest THEN
    RETURN 'WEEK_REST';
  ELSE
    RETURN 'PRESENT';
  END IF;
END;

Now, the error might be a TOO_MANY_ROWS exception and your SELECT statement is returning multiple rows when only a single value is expected; however, we can't tell that because: you haven't posted the error; and the error is being hidden behind catching the OTHERS exception (have I mentioned that you shouldn't catch that). If that is the case, you will need to work out which value you want to return between the date range; that is not something we can help you with as (a) we don't have your data and (b) it is a business decision on how to implement which value of several you return and we can't make that decision for you.

Or, it could be that there is a NO_DATA_FOUND exception. Again, you need to take a business decision on how to handle this but you can use something like:

FUNCTION CF_WEEKRESTFormula
RETURN CHAR
IS
  v_rest     number;
BEGIN
  SELECT week_rest
  INTO   v_rest
  FROM   EMPLOYEES
  WHERE  employee_id1=:employee_id1
  AND    :ATT_DATE between :DATE1 and :DATE2;

  IF TO_CHAR( :ATT_DATE, 'D' ) = v_rest THEN
    RETURN 'WEEK_REST';
  ELSE
    RETURN 'PRESENT';
  END IF;
EXCEPTION
  WHEN NO_DATA_FOUND THEN
    RETURN 'ERROR - NO DATA';
  WHEN TOO_MANY_ROWS THEN
    RETURN 'ERROR - TOO MUCH DATA';
END;