Re: the time zone of systimestamp is not match the user_scheduler_jobs

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);

[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_at_pythian.com
*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_at_gmail.com> 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_at_gmail.com> 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
>>
>>

-- 


--






--
http://www.freelists.org/webpage/oracle-l