oracle-databaseplsqljob-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>