14 Replies Latest reply on Sep 10, 2015 8:38 AM by Maahjoor

    bat file is not running through scheduler

    Maahjoor

      Dear all,

       

      i am using oracle 10g R2 on windows 64 bits.

      i have the following code to run an executeable after every 5 minutes everyday except FRIDAY and SATUREDAY.

      it is executing a bat file. if i double click the bat file, it is working fine. but the scheduler is not executing it.

      begin
        dbms_scheduler.create_schedule(
             schedule_name   => 'populate_free_pc_5_mint',
             repeat_interval => 'FREQ=MINUTELY; INTERVAL=5;
      BYDAY=TUE,WED,THU,SUN,MON;' ,
             comments        => 'schedule to run daily after 5 minutes');
        dbms_scheduler.create_program
          (  program_name   => 'Populate_Free_PC',
             program_type   => 'EXECUTABLE',
             program_action => 'F:\free_access_lab\execute.bat >
      nul',
             enabled        => TRUE,
             comments       => 'Populate the free_pc table'
          );
      
        dbms_scheduler.create_job (
          job_name=>'refresh_free_pc',
          program_name =>'Populate_Free_PC',
              schedule_name=> 'populate_free_pc_5_mint',
          enabled      => true,
              comments     => 'populate the free_pc table after each 5 minutes' 
        );
      end;
      

       

      could somebody guide me what is the problem?

       

      thank you.

        • 1. Re: bat file is not running through scheduler
          GregV

          Hi,

           

          Please check the ALL_SCHEDULER_JOB_RUN_DETAILS view for this job. The ADDITIONAL_INFO column will provide more details regarding a possible problem.

          1 person found this helpful
          • 2. Re: bat file is not running through scheduler
            JohnWatson2

            A batch file is not an executable program. Your program_action needs to be your command shell, which is probably cmd.exe. Then pass the batch file name as a parameter.

            --

            John Watson

            Oracle Certified Master DBA

            1 person found this helpful
            • 3. Re: bat file is not running through scheduler
              Maahjoor

              could you kindly explain please?

              if possible, alter my code above..

               

              thanks a lot. that may be the reason.

               

              Regards.

              • 4. Re: Re: bat file is not running through scheduler
                Maahjoor

                the ADITIONAL_INFO says

                 

                ORA-27369: job of type EXECUTABLE failed with exit code: Incorrect function.

                STANDARD_ERROR="The syntax of the command is incorrect."

                may be as the other friend said that i cannot run bat file directly, executable means exe file which would be cmd.exe in my case.

                 

                could you kindly guide accordingly?

                 

                regards.

                • 5. Re: bat file is not running through scheduler
                  JohnWatson2

                  I'm not going to write your code (uness you want to pay for consulting services). What was not clear in my explanation of the problem and description of the solution?

                  • 6. Re: Re: bat file is not running through scheduler
                    Maahjoor

                    I'm not going to write your code (uness you want to pay for consulting services)

                    Your response is not civilized for this community, but may be your response is the result of my sayings;

                     

                    could you kindly explain please?

                    if possible, alter my code above..

                    may be. but i dont mean to consult you. people around here do help, they do provide code, they do alter the codes of the requester. they do....

                    may be i should have ask in some more courtesy-full words, may be and may be.

                    What was not clear in my explanation of the problem and description of the solution?

                    it was very clear and thorough.

                     

                    Thank you so much for your help.

                     

                    Regards.

                    • 7. Re: Re: Re: bat file is not running through scheduler
                      Maahjoor

                      i have modified my code according to jhonWatson2,

                       

                      begin
                        dbms_scheduler.create_schedule(
                             schedule_name   => 'populate_free_pc_5_mint',
                             repeat_interval => 'FREQ=MINUTELY; INTERVAL=5;
                      BYDAY=TUE,WED,THU,SUN,MON;' ,
                             comments        => 'schedule to run daily after 5 minutes');
                        dbms_scheduler.create_program
                          (  program_name   => 'Populate_Free_PC',
                             program_type   => 'EXECUTABLE',
                             program_action => 'F:\free_access_lab\execute.bat >nul',
                             enabled        => TRUE,
                             comments       => 'Populate the free_pc table'    );
                      dbms_scheduler.create_job( 
                      job_name => 'refresh_free_pc',
                      job_type => 'EXECUTABLE',
                      job_action => 'C:\WINDOWS\system32\cmd.exe',
                      repeat_interval => 'FREQ=MINUTELY; INTERVAL=5;BYDAY=TUE,WED,THU,SUN,MON;',
                      start_date =>'10-Sep-2015 11:17:00 AM',
                      job_class => 'DEFAULT_JOB_CLASS',
                      comments => 'populate the free_pc table after each 5 minutes',
                      auto_drop =>FALSE,
                      number_of_arguments => 1,
                      enabled =>TRUE);
                      dbms_scheduler.set_job_argument_value( job_name => 'refresh_free_pc', argument_position => 1, argument_value => '"F:\free_access_lab\execute.bat"'); 
                      dbms_scheduler.enable( 'refresh_free_pc' ); 
                      end;
                      

                       

                      it generates the following error

                      Error starting at line : 5 in command -

                      begin

                        dbms_scheduler.create_schedule(

                             schedule_name   => 'populate_free_pc_5_mint',

                             repeat_interval => 'FREQ=MINUTELY; INTERVAL=5;

                      BYDAY=TUE,WED,THU,SUN,MON;' ,

                             comments        => 'schedule to run daily after 5 minutes');

                        dbms_scheduler.create_program

                          (  program_name   => 'Populate_Free_PC',

                             program_type   => 'EXECUTABLE',

                             program_action => 'F:\free_access_lab\execute.bat >nul',

                             enabled        => TRUE,

                             comments       => 'Populate the free_pc table'    );

                      dbms_scheduler.create_job(

                      job_name => 'refresh_free_pc',

                      job_type => 'EXECUTABLE',

                      job_action => 'C:\WINDOWS\system32\cmd.exe',

                      repeat_interval => 'FREQ=MINUTELY; INTERVAL=5;BYDAY=TUE,WED,THU,SUN,MON;',

                      start_date =>'10-Sep-2015 11:17:00 AM',

                      job_class => 'DEFAULT_JOB_CLASS',

                      comments => 'populate the free_pc table after each 5 minutes',

                      auto_drop =>FALSE,

                      number_of_arguments => 1,

                      enabled =>TRUE);

                      dbms_scheduler.set_job_argument_value( job_name => 'refresh_free_pc', argument_position => 1, argument_value => '"F:\free_access_lab\execute.bat"');

                      dbms_scheduler.enable( 'refresh_free_pc' );

                      end;

                      Error report -

                      ORA-27457: argument 1 of job "SYS.REFRESH_FREE_PC" has no value

                      ORA-06512: at "SYS.DBMS_ISCHED", line 99

                      ORA-06512: at "SYS.DBMS_SCHEDULER", line 262

                      ORA-06512: at line 13

                      27457. 00000 -  "argument %s of job \"%s\".\"%s\" has no value"

                      *Cause:    No value was provided for the job argument with the specified

                                 position.

                      *Action:   Provide a value for the job argument using any of the

                                 set_job_xxxx_value() routines. Or, when using a named program,

                                 specify a default value for the corresponding argument of the

                                 program.

                      i think the problem is in the line

                      start_date =>'10-Sep-2015 11:17:00 AM',

                      my v$NLS_PARAMETERS view is below

                       

                      NLS_LANGUAGE    AMERICAN

                      NLS_TERRITORY    AMERICA

                      NLS_CURRENCY    $

                      NLS_ISO_CURRENCY    AMERICA

                      NLS_NUMERIC_CHARACTERS    .,

                      NLS_CALENDAR    GREGORIAN

                      NLS_DATE_FORMAT    DD-MON-RR

                      NLS_DATE_LANGUAGE    AMERICAN

                      NLS_CHARACTERSET    AR8MSWIN1256

                      NLS_SORT    BINARY

                      NLS_TIME_FORMAT    HH.MI.SSXFF AM

                      NLS_TIMESTAMP_FORMAT    DD-MON-RR HH.MI.SSXFF AM

                      NLS_TIME_TZ_FORMAT    HH.MI.SSXFF AM TZR

                      NLS_TIMESTAMP_TZ_FORMAT    DD-MON-RR HH.MI.SSXFF AM TZR

                      NLS_DUAL_CURRENCY    $

                      NLS_NCHAR_CHARACTERSET    AL16UTF16

                      NLS_COMP    BINARY

                      NLS_LENGTH_SEMANTICS    BYTE

                      NLS_NCHAR_CONV_EXCP    FALSE

                      what is wrong here?

                       

                      regards.

                      • 8. Re: Re: bat file is not running through scheduler
                        GregV

                        Just try something like:

                         

                        program_action => 'C:\WINDOWS\SYSTEM32\CMD.exe /c F:\free_access_lab\execute.bat > nul'

                        • 9. Re: bat file is not running through scheduler
                          JohnWatson2

                          I think you are nearly there with this,

                           

                          dbms_scheduler.create_job(

                          job_name => 'refresh_free_pc',

                          job_type => 'EXECUTABLE',

                          job_action => 'C:\WINDOWS\system32\cmd.exe',

                          repeat_interval => 'FREQ=MINUTELY; INTERVAL=5;BYDAY=TUE,WED,THU,SUN,MON;',

                          start_date =>'10-Sep-2015 11:17:00 AM',

                          job_class => 'DEFAULT_JOB_CLASS',

                          comments => 'populate the free_pc table after each 5 minutes',

                          auto_drop =>FALSE,

                          number_of_arguments => 1,

                          enabled =>TRUE);

                          dbms_scheduler.set_job_argument_value( job_name => 'refresh_free_pc', argument_position => 1, argument_value => '"F:\free_access_lab\execute.bat"');

                          dbms_scheduler.enable( 'refresh_free_pc' );

                          end;

                           

                          but you need to pass two arguments: the first is " /c " and the second is the name of the file. It is a bit confusing, though - are you wanting to use a Schedule and a Program, or to define all te details in the Job?

                          1 person found this helpful
                          • 10. Re: Re: bat file is not running through scheduler
                            Maahjoor

                            but you need to pass two arguments: the first is " /c "

                            why? somebody has included the /q as well. i dont know the purposes of these.

                             

                            It is a bit confusing, though - are you wanting to use a Schedule and a Program, or to define all te details in the Job?

                            yes , true, i have mixed up the things i think. i would revise it.

                            i was following this thread

                            Running a Windows BAT file using DBMS_SCHEDULER

                             

                            i will recheck my code.

                             

                            Thanks.

                            • 11. Re: Re: bat file is not running through scheduler
                              Maahjoor

                              Great!!

                               

                              i really appreciate your kind help, time and guidance.

                              the full and corrected code is

                              begin
                                dbms_scheduler.create_schedule(
                                     schedule_name   => 'populate_free_pc_5_mint',
                                     repeat_interval => 'FREQ=MINUTELY; INTERVAL=5;
                              BYDAY=TUE,WED,THU,SUN,MON;' ,
                                     comments        => 'schedule to run daily after 5 minutes');
                                dbms_scheduler.create_program
                                  (  program_name   => 'Populate_Free_PC',
                                     program_type   => 'EXECUTABLE',
                                     program_action => 'C:\WINDOWS\SYSTEM32\CMD.exe /c F:\free_access_lab\execute.bat >nul',
                                     enabled        => TRUE,
                                     comments       => 'Populate the free_pc table'    );
                              dbms_scheduler.create_job (  
                                  job_name=>'refresh_free_pc',  
                                  program_name =>'Populate_Free_PC',  
                                      schedule_name=> 'populate_free_pc_5_mint',  
                                  enabled      => true,  
                                      comments     => 'populate the free_pc table after each 5 minutes'   
                                );  
                                dbms_scheduler.enable( 'refresh_free_pc' ); 
                                end;
                              

                               

                              thank you so much dear. it worked.

                               

                               

                              Regards.

                              • 12. Re: Re: bat file is not running through scheduler
                                JohnWatson2

                                Maahjoor wrote:

                                 

                                but you need to pass two arguments: the first is " /c "

                                why? somebody has included the /q as well. i dont know the purposes of these.

                                 

                                <snip>

                                 

                                Thanks.

                                This is explained in the Windows help:

                                 

                                C:\Users\john>  cmd /?

                                Starts a new instance of the Windows command interpreter

                                 

                                CMD [/A | /U] [/Q] [/D] [/E:ON | /E:OFF] [/F:ON | /F:OFF] [/V:ON | /V:OFF]

                                    [[/S] [/C | /K] string]

                                 

                                /C      Carries out the command specified by string and then terminates

                                /K      Carries out the command specified by string but remains

                                /S      Modifies the treatment of string after /C or /K (see below)

                                /Q      Turns echo off

                                1 person found this helpful
                                • 13. Re: Re: Re: bat file is not running through scheduler
                                  Maahjoor

                                  i am using external bat file. could i put and execute the code inside it directly in database so i no more needing the external bat file.

                                  it will help me if i am changing my database from one server to another. i mean i want everthing in may database.

                                  my files are below

                                   

                                  Test.sql

                                  COLUMN data_file NEW_VALUE data_file
                                  truncate table free_pc;
                                  SELECT '\\a server ip address\stdlog$\login_student' || TO_CHAR (SYSDATE, 'DD_MM_YY') || '.txt' AS data_file FROM DUAL
                                  /
                                  HOST SQLLDR scott/tiger@orcl CONTROL=f:\free_access_lab\test.ctl DATA="&data_file" LOG=f:\free_access_lab\test.log
                                  CLEAR COLUMNS
                                  exit
                                  
                                  

                                   

                                  Test.ctl

                                  LOAD DATA
                                  APPEND
                                  INTO TABLE free_pc
                                  WHEN (log_lab='N418')
                                  FIELDS
                                  TRAILING NULLCOLS
                                  (login POSITION(1) TERMINATED BY WHITESPACE,
                                  log_day  FILLER TERMINATED BY WHITESPACE,
                                  log_date BOUNDFILLER TERMINATED BY WHITESPACE,
                                  log_time BOUNDFILLER TERMINATED BY WHITESPACE,
                                  log_user TERMINATED BY WHITESPACE,
                                  log_lab POSITION(*) CHAR(4),
                                  log_pc POSITION(*) TERMINATED BY WHITESPACE,
                                  log_status TERMINATED BY WHITESPACE,
                                  log_date_time "TO_DATE (:log_date||:log_time, 'MM/DD/YYYYHH24:MI:SS')")
                                  INTO TABLE free_pc
                                  WHEN  (log_lab='N419')
                                  FIELDS
                                  TRAILING NULLCOLS
                                  (login POSITION(1) TERMINATED BY WHITESPACE,
                                  log_day  FILLER TERMINATED BY WHITESPACE,
                                  log_date BOUNDFILLER TERMINATED BY WHITESPACE,
                                  log_time BOUNDFILLER TERMINATED BY WHITESPACE,
                                  log_user TERMINATED BY WHITESPACE,
                                  log_lab POSITION(*) CHAR(4),
                                  log_pc POSITION(*) TERMINATED BY WHITESPACE,
                                  log_status TERMINATED BY WHITESPACE,
                                  log_date_time "TO_DATE (:log_date||:log_time, 'MM/DD/YYYYHH24:MI:SS')")
                                  INTO TABLE free_pc
                                  WHEN  (log_lab='A115')
                                  FIELDS
                                  TRAILING NULLCOLS
                                  (login POSITION(1) TERMINATED BY WHITESPACE,
                                  log_day  FILLER TERMINATED BY WHITESPACE,
                                  log_date BOUNDFILLER TERMINATED BY WHITESPACE,
                                  log_time BOUNDFILLER TERMINATED BY WHITESPACE,
                                  log_user TERMINATED BY WHITESPACE,
                                  log_lab POSITION(*) CHAR(4),
                                  log_pc POSITION(*) TERMINATED BY WHITESPACE,
                                  log_status TERMINATED BY WHITESPACE,
                                  log_date_time "TO_DATE (:log_date||:log_time, 'MM/DD/YYYYHH24:MI:SS')")
                                  INTO TABLE free_pc
                                  WHEN (log_lab='B115')
                                  FIELDS
                                  TRAILING NULLCOLS
                                  (login POSITION(1) TERMINATED BY WHITESPACE,
                                  log_day  FILLER TERMINATED BY WHITESPACE,
                                  log_date BOUNDFILLER TERMINATED BY WHITESPACE,
                                  log_time BOUNDFILLER TERMINATED BY WHITESPACE,
                                  log_user TERMINATED BY WHITESPACE,
                                  log_lab POSITION(*) CHAR(4),
                                  log_pc POSITION(*) TERMINATED BY WHITESPACE,
                                  log_status TERMINATED BY WHITESPACE,
                                  log_date_time "TO_DATE (:log_date||:log_time, 'MM/DD/YYYYHH24:MI:SS')")
                                  
                                  

                                   

                                  bat file

                                  sqlplus freepc/freepc@hctorcl @E:\free_pc\test.sql
                                  
                                  

                                   

                                  i run the bat file and the bat file in trun open sqlplus and run the test.sql file which in turn run the test.ctl file.

                                  could i put the above code inside database rather than creating a bat file?

                                   

                                  Regards.

                                  • 14. Re: Re: Re: bat file is not running through scheduler
                                    Maahjoor

                                    Thank you jhon.

                                    its really helpful.

                                     

                                    Regards.