Question

Oracle Scheduler Job Fails to Run: Not an Appropriate Date Error

I am trying to create a scheduler job in Oracle. The job gets created and appears in the list of jobs, but it fails to run at the scheduled time, instead giving an error. For example, right now it gives the error '04-JUL-24' is not an appropriate date. How can I fix this issue?

CREATE OR REPLACE PROCEDURE RPPR_DAILY_REP_CALL AS
BEGIN
  DBMS_SCHEDULER.CREATE_JOB(JOB_NAME        => 'DAILY_BATCH',
                            JOB_TYPE        => 'PLSQL_BLOCK',
                            JOB_ACTION      => 'BEGIN RPPR_DAILY_REP_BATCH;END;',
                            START_DATE      => SYSTIMESTAMP,
                            REPEAT_INTERVAL => 'FREQ=DAILY; BYHOUR=8; BYMINUTE=0; BYSECOND=0',
                            ENABLED         => TRUE,
                            AUTO_DROP       => FALSE,
                            COMMENTS        => 'JOB TO RUN PROCEDURE AT 8AM');

  DBMS_SCHEDULER.RUN_JOB(JOB_NAME => 'DAILY_BATCH');
END;

ERROR FACED:

ORA-23319: parameter value "04-JUL-24" is not appropriate
ORA-06512: at "SYS.DBMS_JOB", line 84
ORA-06512: at "SYS.DBMS_JOB", line 163
ORA-06512: at "REGREP.RPPR_DAILY_REP_BATCH", line 8
ORA-06512: at line 1
ORA-06512: at "SYS.DBMS_ISCHED", line 209
ORA-06512: at "SYS.DBMS_SCHEDULER", line 594
ORA-06512: at "REGREP.RPPR_DAILY_REP_CALL", line 12
ORA-06512: at line 3

WHILE I RUN

BEGIN
DBMS_SCHEDULER.DROP_JOB('DAILY_BATCH');--ONLY WHEN JOB ALREADY EXISTS
RPPR_DAILY_REP_CALL;
END;
 3  53  3
1 Jan 1970

Solution

 1

I discovered the issue: the procedure RPPR_DAILY_REP_BATCH was submitting jobs using SYSDATE when triggered. The problem arose because SYSDATE was being concatenated improperly in the WHAT section of DBMS_JOB.SUBMIT inside the body of RPPR_DAILY_REP_BATCH. The correct approach is to ensure SYSDATE is treated as a string for concatenation.

Initially, I used:

dbms_job.submit(
    job => v_job,
    what => 'begin prc_submit_job(' || sysdate || '); end;'
);

This caused an error due to the direct inclusion of SYSDATE. To fix this, I converted SYSDATE to a string using TO_CHAR and wrapped it with TO_DATE in the WHAT section. The corrected code is:

dbms_job.submit(
    job => v_job,
    what => 'begin prc_submit_job(to_date(''' || to_char(sysdate, 'DD-MON-YYYY') || ''', ''DD-MON-YYYY'')); end;'
);

This ensures that SYSDATE is properly formatted and concatenated as a string, avoiding the previous error.

2024-07-09
Khurram Raza

Solution

 0

Are you sure that problem is related to dbms_scheduler? I have created your procedure and job in my own environment with only job_action value changed and faced no errors while running it.

Here is the procedure : enter image description here

And this one is result of running the procedure with :

BEGIN 
 DBMS_SCHEDULER.DROP_JOB('DAILY_BATCH');--ONLY WHEN JOB ALREADY EXISTS
 RPPR_DAILY_REP_CALL;
END;

enter image description here

And this is how the job is listed in user_scheduler_jobs: enter image description here

My guess is to examine RPPR_DAILY_REP_BATCH procedure, probably something is happening there.

2024-07-05
Sherzodbek