This is a simple PL/SQL code I use when I want to fit a TZ into a scheduled
job. There may be better options, of course, but this one just works for me:
DECLARE
v_start_date timestamp with time zone;
BEGIN
select localtimestamp at time zone 'US/Eastern' into v_start_date from
dual;
DBMS_SCHEDULER.CREATE_JOB (
job_name => 'OWNER.SCHEDULED_JOB_NAME',
job_type => 'PLSQL_BLOCK',
job_action => 'BEGIN NULL; END; ',
start_date => v_start_date,
repeat_interval => 'freq=weekly;byday=Wed;byhour=10;byminute=00',
enabled => TRUE);
END;
/
[image: Pythian] <http://www.pythian.com/>
*Jose Rodriguez* | Oracle Project Engineer | [image: LinkedIn]
<https://www.linkedin.com/company/pythian>
*t* +1 613 565 8696 <+1+613+565+8696> *ext.* 1393
*m* +34 607 55 49 91 <+34+607+55+49+91>
jrodriguez2@xxxxxxxxxxx
*www.pythian.com* <https://www.pythian.com/>
[image: Pythian] <https://www.pythian.com/email-footer-click>
On Wed, 12 Dec 2018 at 16:49, Joan Hsieh <joanhsieh08@xxxxxxxxx> wrote:
SELECT dbtimezone FROM DUAL;
DBTIMEZONE
------------------
-05:00
Thanks for the response.
Is it possible to change the scheduler_job match the dbtimezone? we can't
shutdown database to change the timezone.
Thanks,
Joan
On Wed, Dec 12, 2018 at 9:55 AM Joan Hsieh <joanhsieh08@xxxxxxxxx> wrote:
Hi Listers,
we have some dbms_scheduler_jobs failed due to the timestamp is not
correct. it throw out tons of errors of ORA-08186: invalid timestamp
specified .
The last_start_date from user_scheduler_jobs returns -4:00,
12-DEC-18 10.43.17.198623 AM -04:00
but the systimestamp from dual returns -5:00. what we should do to get it
sync.?
SQL> select systimestamp from dual;
SYSTIMESTAMP
---------------------------------------------------------------------------
12-DEC-18 09.47.28.000075 AM -05:00
Thank you so much.
Joan
