Function to truncate table on weekdays

oracle, oracle11g, plsql

Solution

IF TRIM(l_day_of_week) in ('Monday','Tuesday','Wednesday',
                       'Thursday','Friday','Saturday')

The default return type is like `CHAR(9)`.. 9 being Maximum length of a day of week (Thursday)

So `Monday` would be appended with spaces!! Unless you use `FMDAY`

So, `TRIM()` would remove all the padded trailing spaces!

You can try out the demo below with the use of 'FM' (Format Mask)

With out FM

SQL> select to_char(sysdate+level,'Day'),length(to_char(sysdate+level,'Day')) as length from dual
  2  connect by level <= 7;

TO_CHAR(SYSDATE+LEVEL,'DAY')             LENGTH
------------------------------------ ----------
Tuesday                                       9
Wednesday                                     9
Thursday                                      9
Friday                                        9
Saturday                                      9
Sunday                                        9
Monday                                        9

7 rows selected.

With FM

SQL> select to_char(sysdate+level,'FMDAY'),length(to_char(sysdate+level,'FMDaY')) as length from dual
  2  connect by level <= 7;

TO_CHAR(SYSDATE+LEVEL,'FMDAY')           LENGTH
------------------------------------ ----------
TUESDAY                                       7
WEDNESDAY                                     9
THURSDAY                                      8
FRIDAY                                        6
SATURDAY                                      8
SUNDAY                                        6
MONDAY                                        6

7 rows selected.

Finally,

`l_day_of_week = to_char(sysdate,'DaY')`

Would Do, You save a Dynamic SQL call!

Problem

I want to write a function to truncate a particular table if a day of week is between Tuesday to Saturday. I tried the below code, but `if` condition is not working. Can someone please help me out. ``` create or replace FUNCTION TRUNCATE_TABLE RETURN NUMBER IS --DECLARE VARIABLES l_day_of_week VARCHAR2(15); l_table_name CONSTANT VARCHAR2(40) := 'ABC'; BEGIN EXECUTE IMMEDIATE 'select (to_char(sysdate,''DaY'')) from dual ' INTO l_day_of_week ; IF l_day_of_week in ('Monday','Tuesday','Wednesday', 'Thursday','Friday','Saturday') THEN EXECUTE IMMEDIATE 'TRUNCATE TABLE ABC' ; dbms_output.put_line(l_day_of_week); END IF; RETURN 0; END; ```

Original source