Geek asks and answers

Weekday table truncation function

I want to write a function to truncate a specific table if the day of the week is from Tuesday to Saturday. I tried the code below, but the condition ifdoes not work. Can someone please help me.

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;
+3
source share
2 answers
IF TRIM(l_day_of_week) in ('Monday','Tuesday','Wednesday',
                       'Thursday','Friday','Saturday')

The type of the return type is the default CHAR(9)..
9 - maximum length of the day of the week (Thursday)

So to Mondaybe added with spaces !! If you are not usingFMDAY

So, it TRIM()will delete all the filled spaces!

You can try the demo below using 'FM' ( Format Mask )

Without 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 you save a dynamic SQL call!

+4

, , DBMS_SCHEDULER :

create or replace PROCEDURE TRUNCATE_TABLE    
IS
BEGIN
  EXECUTE IMMEDIATE 'TRUNCATE TABLE ABC' ;
END;

begin
  dbms_scheduler.create_job(
      job_name => 'JOB_TRUNCATE_TABLE'
     ,job_type => 'PLSQL_BLOCK'
     ,job_action => 'begin truncate_table; end; '

     ,start_date      => to_timestamp_tz('2014-02-24 05:00:00 Europe/Berlin',
                                         'yyyy-mm-dd hh24:mi:ss tzr')
     ,repeat_interval => 'FREQ=DAILY;BYHOUR=22;BYDAY=Mon,Tue,Wed,Thu,Fri'
     ,enabled => TRUE
     ,comments => 'Truncate table job, runs Monday to Friday at 22:00');
end;
+3

More articles:


All Articles