How to create dbms job that runs on weekdays(Mon-Fri) at 5:30 AM

Question

I want to create dbms job using DBMS_JOB.SUBMIT package that runs on weekdays(Mon-Fri) at 5:30 AM. I am not sure what values should be passed in next_Day and interval Can anyone please help?

Answer 1

First we need to create one user defined function as following :-

CREATE OR REPLACE FUNCTION GET_DATE RETURN DATE
is 

V_DAY VARCHAR2(10);
V_DATE DATE;

BEGIN

SELECT (to_char(sysdate,'fmDay')) INTO V_DAY FROM DUAL;

IF(V_DAY='Monday' OR V_DAY='Tuesday' OR V_DAY='Wednesday' OR V_DAY='Thursday') THEN


SELECT trunc(sysdate+1)+05/24+30/1440 INTO V_DATE FROM DUAL;
RETURN V_DATE;

ELSE

SELECT trunc(sysdate+3)+05/24+30/1440 INTO V_DATE FROM DUAL;
RETURN V_DATE;

END IF;

END;
/

After that we need to create job in the following way :-

DECLARE
  XYZ NUMBER;
BEGIN
  SYS.DBMS_JOB.SUBMIT
  ( job       => XYZ 
   ,what      => 'BEGIN PROC_NAME; END;'
   ,next_date => to_date('23/03/2017 05:30:00','dd/mm/yyyy hh24:mi:ss')
   ,interval  => 'GET_DATE'
   ,no_parse  => FALSE
  );

END;
/

Answer 2

Using dbms_schedular package, which is available since 10g, you can use 'repeat_interval' parameter as follows:

BEGIN
    DBMS_SCHEDULER.CREATE_JOB (
            job_name => 'MY_JOB',
            job_type => 'PLSQL_BLOCK',
            job_action => 'your code',
            number_of_arguments => 0,
            start_date => NULL,
            repeat_interval => 'FREQ=WEEKLY;BYTIME=053000;BYDAY=MON,TUE,WED,THU,FRI',
            end_date => NULL,
            enabled => FALSE,
            auto_drop => FALSE,
            comments => '');

END;
/

Furthermore, if you are using an IDE such as SQL Developer, you can easily set the details of your job without worrying about the syntax. In fact, the code above is generated by SQL Developer

UPDATE
Try the following using dbms_job (not tested)

DECLARE
   l_job_number NUMBER;
BEGIN
   dbms_job.submit( 
          job       => l_job_number ,
          what      => 'your code',
          next_date => trunc(sysdate)+05/24+30/1440,
          interval  => CASE WHEN (to_char(sysdate,'Day') IN ('Monday','Tuesday','Wednesday','Thursday','Friday')) THEN trunc(sysdate)+05/24+30/1440 else null end 
          );
END;
/