CODEWITHSUNDEEP
sqloracle-databaseplsqlschedulesql-job
Sapo121
Sapo121

Reputation: 71

oracle job only running manually

PROCEDURE CRIAR_JOB_LIMPA_proc IS
BEGIN
DBMS_SCHEDULER.CREATE_JOB (
    job_name        => 'JOB_LIMPA_TAB',
    job_type        => 'STORED_PROCEDURE',
    job_action      => 'SIIMA_JOB_LIMPA_TAB_pkg' || '.LIMPAR_TAB_proc',
    start_date      => To_Date('12-NOV-2018 11:20 AM', 'dd-mon-yyyy hh:mi AM' AT TIME ZONE 'GMT'),
    repeat_interval => 'FREQ=minutely;interval=3',
     comments             => 'Inserir em tabela backup e apagar na original',
    enabled         => TRUE);
END;

I have this job, that runs well when i execute it manually, but it never starts whenever i set a start date, i have tried setting a timezone and everything but it does not seem to help.

Any ideas of possible issues/fixes?

Thank you

Upvotes: 1

Views: 1070

Answers (1)

Wernfried Domscheit
Wernfried Domscheit

Reputation: 59456

DATE values do not support any time zone so AT TIME ZONE ... in combination with TO_DATE is useless. Try TO_TIMESTAMP_TZ or use TIMESTAMP literals, e.g. start_date => TIMESTAMP '2019-11-12 11:20:00 UTC' or try start_date => SYSTIMESTAMP

Your actual problem is the start_date, look carefully:

start_date => To_Date('12-NOV-2019 11:20 AM', 'dd-mon-yyyy hh:mi AM' AT TIME ZONE 'GMT')

12-NOV-2019 - today we have 12-NOV-2018, so your job will start running in one year.

Please ask the question again in one year if the job still hasn't started this time :-)

I am not sure, perhaps you have to add a semicolon and try PLSQL_BLOCK ratehr than STORED_PROCEDURE, i.e.

job_type => 'PLSQL_BLOCK',
job_action => 'SIIMA_JOB_LIMPA_TAB_pkg.LIMPAR_TAB_proc;'

Upvotes: 5

Related Questions