1 Reply Latest reply: Jun 15, 2012 9:14 AM by spajdy RSS

    ORA-01843: not a valid month when DBMS_SCHEDULER.RUN_JOB

      Hi all,

      i'am working with
      Oracle Database 10g Release - 64bit Production
      PL/SQL Release - Production
      CORE     Production
      TNS for IBM/AIX RISC System/6000: Version - Productio
      NLSRTL Version - Production
      I have already used other jobs without problems so i think permissions and privileges are correct.

      Now i create a new job that call a store procedure like that:
      DBMS_SCHEDULER.create_job (
                     comments=>'Job bla bla bla'
      Then i try to run this way:
      DBMS_SCHEDULER.SET_JOB_ARGUMENT_VALUE (job_name=>'MY_JOB', argument_position=>1, argument_value=>'varcharvalue');
      DBMS_SCHEDULER.SET_JOB_ARGUMENT_VALUE (job_name=>'MY_JOB', argument_position=>2, argument_value=>numbervalue);
      DBMS_SCHEDULER.SET_JOB_ARGUMENT_VALUE (job_name=>'MY_JOB', argument_position=>3, argument_value=>numbervalue);
      DBMS_SCHEDULER.SET_JOB_ARGUMENT_VALUE (job_name=>'MY_JOB', argument_position=>4, argument_value=>numbervalue);
      DBMS_SCHEDULER.SET_JOB_ARGUMENT_VALUE (job_name=>'MY_JOB', argument_position=>5, argument_value=>datevalue);
      DBMS_SCHEDULER.SET_JOB_ARGUMENT_VALUE (job_name=>'MY_JOB', argument_position=>6, argument_value=>'varchar2value');
      The date i pass is 15-JUN-12 and before the run the error ORA-01843: not a valid month is raised

      Thanks to all suggestion or information provided.
      Best Regards
        • 1. Re: ORA-01843: not a valid month when DBMS_SCHEDULER.RUN_JOB
          I have the same problem. It is caused by different NLS settings for session when job was created and session where you setup parameters.
          Be sure that both sessions have same NLS settings especially NLS_DATE_FORMAT.
          In dbms_scheduler_jobs there is column with NLS parameters.
          If you can't assure same NLS settings in both sessions change you DATE parameter to string and put date then as a string in format you want. But don't forget to use to_date(string, format) function in job to convert string back to date.