René Nyffenegger's collection of things on the web
René Nyffenegger on Oracle - Most wanted - Feedback -
 

May 27, 2005: 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 the OracleJobScheduler%ORACLE_SID% service is not started. Starting it should eliminate this error.

Thanks

Thanks to Basile Chandesris who notified my of a typo on this page. This typo is now corrected.

More on Oracle

This is an on Oracle article. The most current articles of this series can be found here.