2

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?

Nik
  • 204
  • 1
  • 7
  • 18
  • use dbms_scheduler. http://stackoverflow.com/questions/4152111/dbms-job-vs-dbms-scheduler – Rene Feb 14 '17 at 07:33

2 Answers2

4

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;
/
Hawk
  • 5,060
  • 12
  • 49
  • 74
  • I want it to create it through dbms_job package because as of now i am not having rights to execute dbms_scheduler package. Can you please help? – Nik Feb 14 '17 at 08:29
  • it's showing an error near " ); " (where the job ends). Just a question - do we need to end the case? – Nik Feb 14 '17 at 09:37
  • interval => CASE WHEN (to_char(sysdate,'Day') IN ('Monday','Tuesday','Wednesday','Thursday','Friday')) THEN '' END --What should be the proper value instead of '' after THEN. This statement does not show any syntax error. – Nik Feb 14 '17 at 09:51
  • @user7349009 Yes, there should be `then ... end` I corrected that – Hawk Feb 14 '17 at 10:01
  • It must be 'fmday' otherwise the text is padded with space character – Wernfried Domscheit Feb 14 '17 at 11:12
  • The job gets executed successfully but does not run on specified time i.e. 5:30? – Nik Feb 20 '17 at 11:07
  • @WernfriedDomscheit Can you please help on this? – Nik Feb 20 '17 at 11:32
  • @Hawk - Can you please relook on this? – Nik Feb 20 '17 at 11:32
  • You must use `CASE WHEN (to_char(sysdate,'fmDay') IN ('Monday', 'Tuesday', 'Wednesday', 'Thursday', 'Friday')) THEN ...` or even better `CASE WHEN (to_char(sysdate,'fmDay', 'nls_date_language = english') IN ('Monday', 'Tuesday', 'Wednesday', 'Thursday', 'Friday')) THEN ...` – Wernfried Domscheit Feb 20 '17 at 13:50
  • @WernfriedDomscheit - It's throwing an ORA-06512 error at line 'Case when.........then...' – Nik Feb 21 '17 at 10:38
  • DECLARE X NUMBER; BEGIN SYS.DBMS_JOB.SUBMIT ( job => X ,what => 'BEGIN schema_name.proc_name; END;' ,next_date => to_date('22/02/2017 05:30:00','dd/mm/yyyy hh24:mi:ss') ,interval => CASE WHEN (to_char(sysdate,'fmDay') IN('Monday','Tuesday','Wednesday','Thursday','Friday')) THEN trunc(sysdate)+05/24+30/1440 else null end ,no_parse => FALSE ); SYS.DBMS_OUTPUT.PUT_LINE('Job Number is: ' || to_char(x)); COMMIT; END; / – Nik Feb 21 '17 at 11:48
  • I tried several ways but none of them worked. Seems to be not possible - go for `DBMS_SCHEDULER` as suggested in answer (and as recommended by Oracle [DBMS_JOB](https://docs.oracle.com/database/121/ARPLS/d_job.htm#ARPLS66558) ). – Wernfried Domscheit Feb 21 '17 at 12:07
  • There should be something for dbms_job too... As i said before i currently don't have access to dbms_scheduler. – Nik Feb 21 '17 at 13:03
0

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;
/
Nik
  • 204
  • 1
  • 7
  • 18