Search code examples
oracleplsqljob-scheduling

When submit an Oracle scheduler job (42/76 PLS-00166: bad format for date, time, timestamp or interval literal) this error message received


The error shows when submit the job inside a procedure, error message (42/76 PLS-00166: bad format for date, time, timestamp or interval literal) shows. Job config is showing following.

DBMS_JOB.SUBMIT (
    job_number => v_job_ID + 1,
    what       => 'BEGIN'|| v_query ||'END',
    next_date  => TRUNC(NEXT_DAY(SYSDATE, 'SUN')) + 2.5/24, -- 2:30 AM,
    interval   => 'NEXT_DAY(TRUNC(SYSDATE), ''SUN'') + 2.5/24',
    comments   => 'Job to run every sunday at 2:30 AM',
    no_parse   => TRUE
);

Solution

  • Well, there are various mistakes you've made, but I don't see what causes error you mentioned.

    SQL> ALTER SESSION SET nls_date_language = 'english';
    
    Session altered.
    
    SQL> SET SERVEROUTPUT ON;
    SQL>
    SQL> DECLARE
      2     v_job_id  NUMBER := 0;
      3     v_query   VARCHAR2 (100) := 'null;';
      4  BEGIN
      5     v_job_id := v_job_id + 1;
      6     DBMS_JOB.SUBMIT (job        => v_job_ID,
      7                      what       => 'BEGIN ' || v_query || ' END',
      8                      next_date  => TRUNC (NEXT_DAY (SYSDATE, 'SUN')) + 2.5 / 24, -- 2:30 AM,
      9                      interval   => 'NEXT_DAY(TRUNC(SYSDATE), ''SUN'') + 2.5/24',
     10                      --comments    => 'Job to run every sunday at 2:30 AM',
     11                      no_parse   => TRUE);
     12
     13     DBMS_OUTPUT.put_line (v_job_id);
     14  END;
     15  /
    2983346
    
    PL/SQL procedure successfully completed.
    
    SQL>
    
    • you can't put v_job_ID + 1 into submit; calculate it elsewhere (see line #5), but - that's in vain because DBMS_JOB returns job ID; it is not that you can set it yourself (see result after line #15)
    • what requires a space between begin and the command itself, otherwise you'll get syntax error
    • comments can't be used in submit (check syntax)
    • your database speaks English, doesn't it? Mine doesn't, so I altered session so that your code works in my database. Consider not to use day names as code might break in other databases
    • you didn't specify database version you use; nowadays, in modern versions, dbms_job is here just for backward compatibility. Consider switching to dbms_scheduler