3

I am trying to set a PL/SQL expression as REPEAT_INTERVAL of my job - unfortunately, it is not working.

I would like to include CASE expression within such a interval, for example, how to set repeat interval for the job starts in a full minute, lets say at 14:17:00, and if it is run on even minute it runs in 30 seconds next time, and if it is run on odd minute it starts on next minute, so the piece of its run schedule would look like:

14:17:00
14:18:00
14:18:30
14:19:00
14:20:00
14:20:30
14:21:00

and so on. I have tried with those expressions:

trunc(sysdate, 'MI') + CASE WHEN mod(to_number(to_char(sysdate, 'MI')), 2)=0 then (1/24/60/2) else (1/24/60) end case
SYSTIMESTAMP + CASE WHEN mod(to_number(to_char(sysdate, 'MI')), 2)=0 then INTERVAL '30' SECOND else INTERVAL '60' SECOND end case

they both work in SQL query, but I am unable to compile the JOB. How should such PL/SQL expression look like?

Alternatively, is there a way to make JOB compute its next run date on runtime? I have also tried to modify start date every time the job runs, but with no success - it looks like the job is using start date only once on its first run, and never again, even if the date is changed for a future date.

Jonathan Leffler
  • 730,956
  • 141
  • 904
  • 1,278
WojtusJ
  • 1,318
  • 1
  • 12
  • 19
  • 2
    While this is certainly a very interesting question, I'm curious - why do you need this? Can't you simply run the job every minute? – Frank Schmitt Feb 21 '14 at 10:35
  • This is the simplified situation - in fact I need to run a job on every friday excluding the situations, where friday is not the last day of a given month, then it should be run on the first day of the following month - I think it would be hard to achieve in calendar expression. Alternatively I will run job every day, and will check if this is the "correct" day, if yes I will run the procedure, if not, I won't. – WojtusJ Feb 21 '14 at 10:43
  • This should be possible using combined schedules - see my answer. – Frank Schmitt Feb 21 '14 at 10:44
  • You're right, in this particular scenario yes, but I will probably have different and more complicated schedules in the future, so I would like to find out how to use those pl/sql expressions in repeat_interval. – WojtusJ Feb 21 '14 at 11:03

2 Answers2

3

As far as I can tell from the documentation, this should be possible if you

  • create two schedules; one for the even minutes, one for the odd minutes
  • have one of the schedules include the other
  • configure your job to use the combined schedule

Example from the documentation:

BEGIN
  dbms_scheduler.create_schedule('embed_sched', repeat_interval =>
    'FREQ=YEARLY;BYDATE=0130,0220,0725');
  dbms_scheduler.create_schedule('main_sched', repeat_interval =>
    'FREQ=MONTHLY;INTERVAL=2;BYMONTHDAY=15;BYHOUR=9,17;INCLUDE=embed_sched');
END;
Frank Schmitt
  • 30,195
  • 12
  • 73
  • 107
3

i guess its a bit late answer but better later than never. You can use plssql function inside the repeat interval:

tested on: 11.2.0.3.0

Lets create function. my_schedules.odd_even it will return current_date + 30 seconds if current minute is even and 1 minute if current minute is odd: 

    CREATE OR REPLACE package my_schedules
is
function odd_even(p_date date default sysdate) return date;
end;
</code>
<code>
CREATE OR REPLACE package body my_schedules  
is

-- even - 30 seconds
-- odd - 1 minute;
function odd_even(p_date date default sysdate) 
return date
is
l_ret date;
l_sec_in_day number := 60*60*24;
begin

case mod( (to_number(to_char(sysdate,'MI'))) ,2)
when 0 then 
--- even return 30 seconds
l_ret:= SYSDATE + 30/l_sec_in_day;
else 
-- odd return a minute
l_ret:= SYSDATE + 60/l_sec_in_day;
end case;
return l_ret;
end;`enter code here`

end;
/

create job with repeat_interval=my_schedules.odd_even and enable the job: 

declare
  l_action varchar2(2000);
  l_repeat_interval varchar2(250) := 'my_schedules.odd_even';
  l_job_name varchar2(30) := 'TESTING_PLSSQL_SCH';
  begin  
    l_action := 'declare dummy number; begin dummy := 1; end;';

    dbms_scheduler.create_job(job_name        => '"'|| l_job_name||'"',
                              job_type        => 'plsql_block',
                              job_action      => l_action,
                              start_date      => sysdate,
                              repeat_interval => l_repeat_interval,
                              comments        => 'just a test'
                              );

    dbms_scheduler.enable(name => '"'||l_job_name||'"');                              
 end;

Let's check results in a while: 

select job_name, actual_start_date  from DBA_SCHEDULER_JOB_RUN_DETAILS rd where job_name = 'TESTING_PLSSQL_SCH' order by actual_start_date;

job_name |  actual_start_date
--------------------------
TESTING_PLSSQL_SCH  2016/07/14/ 17:33:36,671977 +03:00
TESTING_PLSSQL_SCH  2016/07/14/ 17:34:36,007573 +03:00
TESTING_PLSSQL_SCH  2016/07/14/ 17:35:06,006206 +03:00
TESTING_PLSSQL_SCH  2016/07/14/ 17:36:06,001652 +03:00
TESTING_PLSSQL_SCH  2016/07/14/ 17:36:36,005513 +03:00
TESTING_PLSSQL_SCH  2016/07/14/ 17:37:06,003572 +03:00
TESTING_PLSSQL_SCH  2016/07/14/ 17:38:06,011409 +03:00
TESTING_PLSSQL_SCH  2016/07/14/ 17:38:36,011411 +03:00
TESTING_PLSSQL_SCH  2016/07/14/ 17:39:06,011357 +03:00
TESTING_PLSSQL_SCH  2016/07/14/ 17:40:06,002623 +03:00

Oracle Database Online Documentation 11g Release 1 (11.1) / Database Administration/Using Schedules: here

Robby
  • 381
  • 2
  • 3