Running external shell or java Jobs

Using the Oracle package DBMS_SCHEDULE for running internal and external jobs is a great way of controlling what goes on with your database system. However, when running external jobs such as shell scripts or java it is important to understand a couple of things.

1. Oracle Execution Environment
All external jobs are executed by default with a low privileged user called nobody. Oracle uses the definitions found in "$ORACLE_HOME/rdbms/admin/externaljob.ora". By default this user doesn't even have a PATH variable set.

2. Unix Return Value
The exit code should be zero if all is okay. Anything other than zero causes the Job to be displayed as failed. Oracle captures the error output (STDERR) from the job and saves this along with the execution code from each job. This can be a multiline output, so you can do something like this:

echo "Starting $1 at " `date` >&2
...
echo "Finished execution at " `date` >&2
exit 0

3. Parsing Parameters
You can parse parameters (or arguments) to an external program by specifying the "NUMBER_OF_ARGUMENTS" when the job is created. You must also create the job disabled and then add the arguments and set the job to enabled:

BEGIN
DBMS_SCHEDULER.CREATE_JOB (

JOB_NAME => 'MY_TEST',
JOB_TYPE => 'EXECUTABLE',
JOB_ACTION => '/bin/ple_test.sh',
START_DATE => SYSTIMESTAMP,
REPEAT_INTERVAL => 'freq=minutely; byminute=2,12,22,32,42,52;',
END_DATE => NULL,
ENABLED => FALSE,
NUMBER_OF_ARGUMENTS => 1 );

DBMS_SCHEDULER.SET_JOB_ARGUMENT_VALUE('MY_TEST',1,'Hello World');
DBMS_SCHEDULER.ENABLE('MY_TEST');
END;