Database Reference
In-Depth Information
Evidence for the findings reported so far comes from running a small command-line
script, which terminates with exit code 1. The subsequent example is coded for Windows, but
the results apply equally to UNIX. The source code of the Windows command-line script (file
failure.bat
) is reproduced here:
echo This is script %0.
echo About to exit with exit code 1. 1>&2
exit 1
The following PL/SQL block creates an external job, which runs the above script. The
supplied calendaring expression “FREQ=MINUTELY;INTERVAL=3” schedules the job to run
every three minutes. Note that the job is created and run as user SYS.
C:> sqlplus / as sysdba
Connected to:
Oracle Database 11g Enterprise Edition Release 11.1.0.6.0 - Production
SQL> BEGIN
DBMS_SCHEDULER.CREATE_JOB(
job_name => 'failure_test',
job_type => 'EXECUTABLE',
job_action => 'C:\home\ndebes\bin\failure.bat',
start_date => systimestamp,
repeat_interval => 'FREQ=MINUTELY;INTERVAL=3',
enabled=>true /* default false! */
);
END;
/
PL/SQL procedure successfully completed.
The job may be run manually by calling the procedure
DBMS_SCHEDULER.RUN_JOB
.
SQL> EXEC dbms_scheduler.run_job('failure_test')
BEGIN dbms_scheduler.run_job('failure_test'); END;
*
ERROR at line 1:
ORA-27369: job of type EXECUTABLE failed with exit code: Incorrect function.
ORA-06512: at "SYS.DBMS_ISCHED", line 154
ORA-06512: at "SYS.DBMS_SCHEDULER", line 450
ORA-06512: at line 1
Due to the non-zero exit code, the job is classified as “FAILED”. After the preceding
manual job execution and a single scheduled execution, querying
DBA_SCHEDULER_JOB_LOG
and
DBA_SCHEDULER_JOB_RUN_DETAILS
yields the following results:
SQL> SELECT jl.log_id, jl.status, jl.additional_info AS log_addtl_info,
jd.status, jd.additional_info AS details_addtl_info
FROM dba_scheduler_job_log jl, dba_scheduler_job_run_details jd
WHERE jl.job_name='FAILURE_TEST'
