--Doc ID 807159.1 - Sample Code 
--Doc ID 1520580.1 - Master Note: Troubleshooting Oracle Scheduler

--1) Prepare
--
-- if start_date is NULL (timezone from session TIME_ZONE|ORA_SDTZ client env|default_timezone of scheduler)
--
ALTER SESSION SET TIME_ZONE = 'EUROPE/MOSCOW';
exec DBMS_SCHEDULER.SET_SCHEDULER_ATTRIBUTE('default_timezone','EUROPE/MOSCOW');

-- if start_date is NOT NULL (timezone from start_time)
--
alter session set nls_date_format='yyyy-mm-dd hh24:mi:ss';
alter session set NLS_TIMESTAMP_FORMAT='yyyy-mm-dd hh24:mi:ssxff' ;
alter session set NLS_TIMESTAMP_TZ_FORMAT='yyyy-mm-dd hh24:mi:ss TZR TZD';


drop table scott.t21_log purge;
create table scott.t21_log(x number generated as identity, y timestamp with time zone, z varchar2(64));

insert into scott.t21_log(y,z) values
( to_timestamp_tz('20161021 14:00:00 EUROPE/MOSCOW','YYYYMMDD HH24:MI:SS TZR')
 ,to_char(SYSTIMESTAMP AT TIME ZONE 'EUROPE/MOSCOW','YYYYMMDD HH24:MI:SSXFF TZR TZD')
);


--2) Check of preparing

select dbms_scheduler.stime from dual;

STIME
-------------------------------------
2016-10-21 14:09:13 EUROPE/MOSCOW MSK


select * from scott.t21_log;

    X Y                                      Z
----- -------------------------------------- -------------------------------------------
    1 2016-10-21 14:00:00 EUROPE/MOSCOW MSK  20161021 14:09:40,979096 EUROPE/MOSCOW MSK


--3)
declare
 job_does_not_exist exception;
 pragma exception_init(job_does_not_exist, -27475);
begin
 DBMS_SCHEDULER.DROP_JOB ('CALEND_ALA_TEST');
 exception when job_does_not_exist then
   dbms_output.put_line('Create job');
end;
/

ALTER SESSION SET TIME_ZONE = 'EUROPE/MOSCOW';
begin
dbms_scheduler.create_job('CALEND_ALA_TEST'
 ,'PLSQL_BLOCK'
 ,'begin insert into scott.t21_log(y,z) 
    select SYSTIMESTAMP AT TIME ZONE ''EUROPE/MOSCOW''
    ,to_char(systimestamp, ''YYYYMMDD HH24:MI:SSXFF'') from dual;commit; end;'
-- , start_date =>'2016-10-21 13:25:00 EUROPE/MOSCOW'
 ,start_date=>null
 ,enabled => true
-- ,repeat_interval => 'freq=daily;byminute=12');
-- ,repeat_interval => 'freq=hourly;byminute=5');
 ,repeat_interval => 'freq=minutely;bysecond=59');
end;
/

-- в датах никаких смещений: +4, -7 ,...
-- только временные зоны должны быть 
-- вдруг летнее время(DST) вернется
select owner
      ,start_date
      ,repeat_interval
      ,last_start_date
      ,next_run_date 
 from dba_scheduler_jobs where job_name='CALEND_ALA_TEST';

select * from scott.t21_log;

1 2016-10-21 14:00:00 EUROPE/MOSCOW MSK            20161021 14:09:40,979096 EUROPE/MOSCOW MSK
2 2016-10-21 14:39:02 EUROPE/MOSCOW MSK            20161021 14:39:02,623923
3 2016-10-21 14:40:02 EUROPE/MOSCOW MSK            20161021 14:40:02,623461
4 2016-10-21 14:41:02 EUROPE/MOSCOW MSK            20161021 14:41:02,622506
5 2016-10-21 14:42:02 EUROPE/MOSCOW MSK            20161021 14:42:02,621904
6 2016-10-21 14:43:02 EUROPE/MOSCOW MSK            20161021 14:43:02,614655

--проверить календарь 
--зациклить вычисление next_run_date
--example: http://docs.oracle.com/database/121/ARPLS/d_sched.htm#ARPLS72366
ALTER SESSION SET TIME_ZONE = 'EUROPE/MOSCOW';
DECLARE
 start_date        TIMESTAMP WITH TIME ZONE;
 return_date_after TIMESTAMP WITH TIME ZONE;
 next_run_date     TIMESTAMP WITH TIME ZONE;
-- start_date        TIMESTAMP;
-- return_date_after TIMESTAMP;
-- next_run_date     TIMESTAMP;
BEGIN
--start_date := null;
 start_date :=  to_timestamp_tz('20161021 14:00:00  EUROPE/MOSCOW MSK','YYYYMMDD HH24:MI:SS TZR TZD');
 return_date_after := start_date;
FOR i IN 1..5 LOOP
 DBMS_SCHEDULER.EVALUATE_CALENDAR_STRING(  
    'FREQ=DAILY;BYHOUR=9;BYMINUTE=30;BYDAY=MON,TUE,WED,THU,FRI',
    start_date, return_date_after, next_run_date);
-- DBMS_OUTPUT.PUT_LINE('next_run_date: ' || to_char(next_run_date AT TIME ZONE 'EUROPE/MOSCOW','YYYYMMDD HH24:MI:SSXFF TZR'));
 DBMS_OUTPUT.PUT_LINE('next_run_date: ' || next_run_date);
 return_date_after := next_run_date;
END LOOP;
END;
/

next_run_date: 2016-10-24 09:30:00 EUROPE/MOSCOW MSK
next_run_date: 2016-10-25 09:30:00 EUROPE/MOSCOW MSK
next_run_date: 2016-10-26 09:30:00 EUROPE/MOSCOW MSK
next_run_date: 2016-10-27 09:30:00 EUROPE/MOSCOW MSK
next_run_date: 2016-10-28 09:30:00 EUROPE/MOSCOW MSK

begin
 DBMS_SCHEDULER.DROP_JOB ('CALEND_ALA_TEST');
end;
/

drop table scott.t21_log purge;