April 2008 Archives

In Oracle 10g R2 their would appear to be a bug when enabling a job via a user owned procedure like this:
BEGIN
MYUSER.PKG_JOBS.ENABLE_JOBS;
END;
which my activate a whole bunch of jobs in a schedule.

Say the MYUSER has been granted MYROLE which is defined as:
grant create job,
execute any program,
execute any class,
create external job,
manage scheduler TO MYROLE;

In Order for the procedure call above to execute a:
DBMS_SCHEDULER.ENABLE ( 'MYJOB' );
which uses an EXECUTABLE (external) shell or program to run you must grant the 'create external job' directly to MYUSER.

Interestingly if MYUSER enables the job by calling DBMS_SCHEDULER.ENABLE directly it works fine but not if the enable is stored in a package which belongs to MYUSER.

This useful "GUIDE TO RUNNING EXTERNAL JOBS ON 10g WITH DBMS_SCHEDULER" may help in understanding what issues are involved with calling external jobs under Oracle.

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;