Something is going wrong with my procedure in Oracle, day of week problem

Question

I have a table named MEAKEL with two columns:

ID  NUMBER(3,0)
DAY VARCHAR2(10 CHAR)

A row will look like this:

111, sunday

I got a procedure to get the key and compare it to current day

CREATE OR REPLACE PROCEDURE check_key_day(p_key IN NUMBER) IS
  v_day VARCHAR2(10);
  v_today VARCHAR2(10);
BEGIN
  SELECT DAY INTO v_day
  FROM MEAKEL
  WHERE ID = p_key;
  
  v_today := LOWER(TO_CHAR(SYSDATE, 'day'));
    DBMS_OUTPUT.PUT_LINE('v_day = ' || v_day);
  DBMS_OUTPUT.PUT_LINE('Today = ' || v_today);

  IF v_day != v_today THEN
    RAISE_APPLICATION_ERROR(-20001, 'The day stored in the table does not match today''s day');
  END IF;
END;

set serveroutput on
BEGIN
check_key_day(111);
END;

Actually the two output.put_line return "sunday":

v_day = sunday
Today = sunday   

I have no idea why RAISE_APPLICATION_ERROR is raised because the condition is when NOT EQUAL, ...

What am I doing wrong?

Answer 1

It is date format model you used that bothers you. 'day' returns day name, but it is right-padded with spaces up to length of the longest day name, which means that all days names have the same length (see day_name_length column in example that follows; all values are 9). It means that your code would work on wednesdays.

But, if you used 'fmday', then you'd get a different result and comparison of current day name and value stored in table would work every day.

SQL> select to_char(sysdate, 'day') || '#' day_1,
  2         to_char(sysdate, 'fmday') || '#' day_2
  3  from dual;

DAY_1                                 DAY_2
------------------------------------- -------------------------------------
sunday   #                            sunday#
      ---    
      'day': see spaces here?         'fmday': no spaces

---

SQL> with temp (datum) as
  2    (select sysdate + level - 1
  3     from dual
  4     connect by level <= 7
  5    )
  6  select to_char(datum, 'dd.mm.yyyy') date_1,
  7    to_char(datum, 'day') day_name,
  8    length(to_char(datum, 'day')) day_name_length,
  9    --
 10    to_char(datum, 'fmday') day_name_2,
 11    length(to_char(datum, 'fmday')) day_name_length_2
 12  from temp
 13  order by datum;

DATE_1     DAY_NAME     DAY_NAME_LENGTH DAY_NAME_2   DAY_NAME_LENGTH_2
---------- ------------ --------------- ------------ -----------------
12.02.2023 sunday                     9 sunday                       6
13.02.2023 monday                     9 monday                       6
14.02.2023 tuesday                    9 tuesday                      7
15.02.2023 wednesday                  9 wednesday                    9
16.02.2023 thursday                   9 thursday                     8
17.02.2023 friday                     9 friday                       6
18.02.2023 saturday                   9 saturday                     8
                                      ^                              ^
7 rows selected.                      |                              |
                                    'day'                          'fmday'
SQL>

Answer 2

Ive done the following eventually instead of trying to compare sunday with sunday Im comparing 1 with 1, and it seems to be working fine.

CREATE OR REPLACE PROCEDURE check_key_day(p_key IN NUMBER) IS
  v_day number(1);
  v_today number(1);
BEGIN
  SELECT DAY INTO v_day
  FROM MEAKEL
  WHERE ID = p_key;
  
   v_today := TO_CHAR(SYSDATE, 'D');

  IF v_day != v_today THEN
    RAISE_APPLICATION_ERROR(-20001, 'The day stored in the table does not match today''s day');
  END IF;
END;


set serveroutput on
BEGIN
check_key_day(102);
END;


CREATE TABLE MEAKEL (
    ID NUMBER(3) NOT NULL,
    DAY NUMBER(1) NOT NULL,
    CONSTRAINT MEAKEL_PK PRIMARY KEY (ID)
);