oracleplsqljobsdbms-job

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


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?


Solution

  • 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;
    /