On making Oracle start a batch file (.bat) on Windows

 With Oracle 10g and dbms_scheduler it is possible to start a shell script (Unix) or a batch file (Windows). The following article tries to show how to do that for Windows.

More specifically, the batch file will delete the alert_log.html. Here's its content:

c:\ora_utils\del_alert.bat

del D:\oracle\databases\ora10\bdump\alert_ora10.log

This batch file can then be executed like so:

begin
  dbms_scheduler.create_job (
    job_name     =>'DEL_ALERT_LOG',
    job_type     =>'executable',
    job_action   =>'c:\windows\system32\cmd.exe /c c:\ora_utils\delete_alert_log.bat > nul',
    enabled      => true,
    auto_drop    => true
);
commit;
end;
/

The redirection of the batch's output to nul (> nul) seems to be important in order for the function to run. When I omitted it, I received an ORA-27369: job of type EXECUTABLE failed with exit code: Incorrect function ORA-27369: job of type EXECUTABLE failed with exit code: Incorrect function.

user_scheduler_job_run_details

user_scheduler_job_run_details can be used to find out if the job has run without error:

set linesize 600

select status, error#, substr(additional_info,1,500) 
  from user_scheduler_job_run_details 
 where job_name = 'DEL_ALERT_LOG';

STATUS                             ERROR# SUBSTR(ADDITIONAL_INFO,1,500)
----------------------------------------------------------------------------------------------------------------------------------
FAILED                              27370 ORA-27370: job slave failed to launch a job of type EXECUTABLE
                                          ORA-27300: OS system dependent operation:accessing execution agent failed with status: 2
                                          ORA-27301: OS failure message: The system cannot find the file specified.
                                          ORA-27302: failure occurred at: sjsec 6
                                          ORA-27303: additional information: The system cannot find the file specified.

In this case, an ORA_27370 is reported. An ORA_27370 has probably to do that theOracleJobScheduler%ORACLE_SID% service is not started. Starting it should eliminate this error.

Reference: http://www.adp-gmbh.ch/blog/2005/may/27.html