oracle-databasejob-scheduling

oracle job "next run date"


My oracle database time zone is +03:30 (TEHRAN)

select current_timestamp from dual

22-JAN-24 11.06.09.128998 AM +03:30


 dbms_scheduler.create_job (
              job_name          => v_job_name ,
              job_type          => 'PLSQL_BLOCK',
              job_class         => 'DEFAULT_JOB_CLASS',
              job_action        => v_stmt,
              start_date        => SYSDATE,
              enabled           => TRUE,
              auto_drop         => TRUE
              );

but when we set a job to be runned immediately in my current sysdate ,My job run after 3.5 hours !

but we expect that my job run immediatly;


Solution

  • "My oracle database time zone is +03:30 (TEHRAN)" - What does it mean?

    See How to handle Day Light Saving in Oracle database

    current_timestamp returns the current time in your SESSIONTIMEZONE, however SYSDATE returns the current time in the time zone of database server's operating system.

    Parameter start_date has data type TIMESTAMP WITH TIME ZONE, your input value is converted implicitly as

    FROM_TZ(CAST(SYSDATE AS TIMESTAMP), SESSIONTIMEZONE)
    

    However, in principle you would require

    FROM_TZ(CAST(SYSDATE AS TIMESTAMP), TO_CHAR(SYSTIMESTAMP, 'tzr'))
    

    Try

    dbms_scheduler.create_job (
              job_name          => v_job_name ,
              job_type          => 'PLSQL_BLOCK',
              job_class         => 'DEFAULT_JOB_CLASS',
              job_action        => v_stmt,
              start_date        => CURRENT_TIMESTAMP,
              enabled           => TRUE,
              auto_drop         => TRUE
              );
    

    or

    dbms_scheduler.create_job (
              job_name          => v_job_name ,
              job_type          => 'PLSQL_BLOCK',
              job_class         => 'DEFAULT_JOB_CLASS',
              job_action        => v_stmt,
              start_date        => SYSTIMESTAMP,
              enabled           => TRUE,
              auto_drop         => TRUE
              );
    

    Either of them should work.