10 Replies Latest reply on Jul 13, 2011 12:29 PM by 300230

    Running a Windows BAT file using DBMS_SCHEDULER

    Antilles
      My Oracle db is on a Windows machine, and I'm trying to call a DOS BAT file using DBMS_SCHEDULER

      I'm new to Oracle but the code I've worked out so far is:

      BEGIN
      DBMS_SCHEDULER.drop_job ('testjob');
      DBMS_SCHEDULER.CREATE_JOB (
      job_name => 'testjob',
      job_type => 'EXECUTABLE',
      job_action =>'c:\windows\system32\cmd.exe /c c:\test.bat > nul',
      auto_drop=>false,
      enabled => TRUE
      );
      DBMS_SCHEDULER.run_job ('testjob');
      END;

      And I've granted the CREATE EXTERNAL JOB permission to my user.

      The test BAT file I'm calling simply deletes another file (just to prove the concept that I can actually run the BAT file). The above code appears to run fine (in SQL Developer) but the BAT file doesn't actually seem to do anything. If I run the BAT file manually (by double clicking), it deletes the target file. But when running through Oracle it never does.

      If I change the name of test.bat in the above code to a file which doesn't exist, I get an error - proving that at least Oracle is finding the BAT file.

      Any ideas? Anyone got something similar to work on a Windows box?
        • 1. Re: Running a Windows BAT file using DBMS_SCHEDULER
          355679
          Could you check the OracleJobScheduler Windows service

          From the Windows Platform guide for 10gR2

          This release includes a new database scheduler to provide enterprise scheduling functionality. External jobs performed by the user are started using the OracleJobScheduler service. This service is disabled by default. In order to use the external jobs functionality, the administrator must set the username and password for the user account under which this service must run and enable the service.

          Restricting execution of external jobs to a low-privileged user prevents unauthorized database users from gaining operating system level privileges, but it also places restrictions on the kinds of jobs that can be run. Jobs requiring a higher level of operating system privileges cannot be run by this mechanism.
          1 person found this helpful
          • 2. Re: Running a Windows BAT file using DBMS_SCHEDULER
            Centinul
            We use DOS batch files as well via Oracle database jobs. Here is an example:
            BEGIN
            
            sys.dbms_scheduler.create_job( 
            
            job_name => 'testjob',
            
            job_type => 'EXECUTABLE',
            
            job_action => 'C:\WINDOWS\system32\cmd.exe',
            
            repeat_interval => 'FREQ=WEEKLY;BYDAY=MON,TUE,WED,THU,FRI;BYHOUR=5;BYMINUTE=0;BYSECOND=0',
            
            start_date => systimestamp at time zone 'US/Eastern',
            
            job_class => 'DEFAULT_JOB_CLASS',
            
            comments => 'test job',
            
            auto_drop => FALSE,
            
            number_of_arguments => 3,
            
            enabled => FALSE);
            
            sys.dbms_scheduler.set_job_argument_value( job_name => 'testjob', argument_position => 1, argument_value => '/q'); 
            
            sys.dbms_scheduler.set_job_argument_value( job_name => 'testjob', argument_position => 2, argument_value => '/c'); 
            
            sys.dbms_scheduler.set_job_argument_value( job_name => 'testjob', argument_position => 3, argument_value => '"test.bat"'); 
            
            sys.dbms_scheduler.enable( 'testjob' ); 
            
            END;{code}Basically you need to remove your batch file from the JOB_ACTION section and place it in the set_job_argument_value portion of the job creation.
            
            I hope this helps!                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                            
            • 3. Re: Running a Windows BAT file using DBMS_SCHEDULER
              Antilles
              jdanton - the service is running, I eventually across this problem this morning so enabled and started it.
              Centinul - I'll have a look at your script and see if this works for me. Will let you know how I get on.
              • 4. Re: Running a Windows BAT file using DBMS_SCHEDULER
                LKBrwn_DBA
                Or..... you could just go to OEM> Jobs> Create Job> OS Command>. ;)
                1 person found this helpful
                • 5. Re: Running a Windows BAT file using DBMS_SCHEDULER
                  108476
                  Hi,

                  Ken Naim has a working example of a Windows bat file invocation here:

                  http://www.freelists.org/archives/oracle-l/04-2006/msg00122.html

                  Hope this helps. . .

                  Donald K. Burleson
                  Oracle Press author
                  Author of "Oracle Tuning: The Definitive Reference":
                  1 person found this helpful
                  • 6. Re: Running a Windows BAT file using DBMS_SCHEDULER
                    Antilles
                    Thankyou for all the helpful replies. I almost have centinul's example working.

                    But again the job appears to run fine, (SUCCESS is shown in dba_scheduler_job_run_details), but ADDITIONAL_INFO shows:

                    STANDARD_ERROR="Could Not Find C:\WINDOWS\system32\*.xyz"

                    .. and the bat file doesn't actually run.

                    (My BAT file has just one line - del *.xyz , so it looks like something is going screwy here).

                    Any ideas (I'm sure the answer is staring me in the face)?
                    • 7. Re: Running a Windows BAT file using DBMS_SCHEDULER
                      Antilles
                      Never mind.. got it working. The DOS script wasn't quite right - I needed to change the path in it.

                      Centinul - your example worked a treat. Thankyou.

                      Thanks also for all the other replies, all helpful and a few alternative methods in there.
                      • 8. Re: Running a Windows BAT file using DBMS_SCHEDULER
                        761823
                        Hi,
                        I am having the same problem now. What value do I provide for Argument Position 1 and position 2. I know position 3 gets the value " c:\fred_home\test.bat"; .
                        I would greatly your feedback and help in this matter.
                        thanks, fred
                        • 9. Re: Running a Windows BAT file using DBMS_SCHEDULER
                          863772
                          How about try to login with "sys" ,and connect as sysdba.
                          • 10. Re: Running a Windows BAT file using DBMS_SCHEDULER
                            300230
                            Hi All
                            I know this thread is old but my issue is somewhat related to the original post.
                            I have a batch file copies fileA from server A to server B.
                            Case one:
                            Batch file "copyfile.bat" is on Server A, file to copy is on server A in a different directory on same server.

                            Batch file content.
                            xcopy p:\schedule_jobs\fileA.txt c:\
                            BEGIN
                              DBMS_SCHEDULER.CREATE_JOB (
                              JOB_NAME=> 'myjob',
                              JOB_TYPE=> 'executable',
                              JOB_ACTION=> 'c:\windows\system32\cmd.exe /c P:\schedule_jobs\copyfile.bat > nul',
                              ENABLED=> TRUE,
                              AUTO_DROP=>TRUE);
                            END;
                            Works both manually and through above code. Great.

                            Case Two.
                            Batch file "copyfile.bat" is on Server A, file to copy is on server B

                            Batch file content.
                            xcopy p:\schedule_jobs\fileA.txt \\serverB\others\
                            BEGIN
                              DBMS_SCHEDULER.CREATE_JOB (
                              JOB_NAME=> 'myjob',
                              JOB_TYPE=> 'executable',
                              JOB_ACTION=> 'c:\windows\system32\cmd.exe /c P:\schedule_jobs\copyfile.bat > nul',
                              ENABLED=> TRUE,
                              AUTO_DROP=>TRUE);
                            END;
                            It works manually (double clicking) copies fileA from serverA to serverB but not by above code.
                            No errors.
                            OS windows 64bit, Oracle 10g Release 10.2.0.4.0
                            I have a question on Centinul code, what does the *"/q"* stands for in the argument value argument_value => '/q' I think argument_value => '/c' (c is close after performing).
                            In my case if I use Centinul code how many arguments do I have to use, like example
                            1. file_name 2. file_location 3. close after *'/c'*

                            Thanks to all.

                            Habeeb*