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

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

    OraclePSP
      Hi all,

      i'am working with
      Oracle Database 10g Release 10.2.0.3.0 - 64bit Production
      PL/SQL Release 10.2.0.3.0 - Production
      CORE     10.2.0.3.0     Production
      TNS for IBM/AIX RISC System/6000: Version 10.2.0.3.0 - Productio
      NLSRTL Version 10.2.0.3.0 - 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 (
                     job_name=>'MY_JOB',
                     job_type=>'STORED_PROCEDURE',
                     job_action=>'MY_PKG.myProcedure',
                        number_of_arguments=>6,
                     auto_drop=>FALSE,
                        enabled=>FALSE,
                     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');
      DBMS_SCHEDULER.ENABLE('MY_JOB');
      DBMS_SCHEDULER.RUN_JOB('MY_JOB');
      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
      Alex
        • 1. Re: ORA-01843: not a valid month when DBMS_SCHEDULER.RUN_JOB
          spajdy
          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.