Using Jobs

A job is the combination of a schedule and a program, along with any additional arguments required by the program. This section introduces you to basic job tasks, and discusses the following topics:

Job Tasks and Their Procedures
Creating Jobs
Copying Jobs
Altering Jobs
Running Jobs
Stopping Jobs
Dropping Jobs
Disabling Jobs
Enabling Jobs

Job Tasks and Their Procedures

Table 27-1 illustrates common job tasks and their appropriate procedures and privileges:

Table 27-1 Job Tasks and Their Procedures

Task	Procedure	Privilege Needed
Create a job	`CREATE_JOB` or `CREATE_JOBS`	`CREATE` `JOB` or `CREATE` `ANY` `JOB`
Alter a job	`SET_ATTRIBUTE` or `SET_JOB_ATTRIBUTES`	`ALTER` or `CREATE` `ANY` `JOB` or be the owner
Run a job	`RUN_JOB`	`ALTER` or `CREATE` `ANY` `JOB` or be the owner
Copy a job	`COPY_JOB`	`ALTER` or `CREATE` `ANY` `JOB` or be the owner
Drop a job	`DROP_JOB`	`ALTER` or `CREATE` `ANY` `JOB` or be the owner
Stop a job	`STOP_JOB`	`ALTER` or `CREATE` `ANY` `JOB` or be the owner
Disable a job	`DISABLE`	`ALTER` or `CREATE` `ANY` `JOB` or be the owner
Enable a job	`ENABLE`	`ALTER` or `CREATE` `ANY` `JOB` or be the owner

See "Scheduler Privileges" for further information regarding privileges.

Creatin g Jobs

You create one or more jobs using the CREATE_JOB or CREATE_JOBS procedures or Enterprise Manager. The CREATE_JOB procedure is used to create a single job. This procedure is overloaded to enable you to create different types of jobs that are based on different objects. Multiple jobs can be created using the CREATE_JOBS procedure.

For each job being created, you specify a job type, an action, a schedule, and other attributes. The job type specifies whether to create a regular job or a lightweight job. If you do specify a job type, the default type is regular.

For example, the following statement creates a single job called update_sales, which calls a stored procedure in the OPS schema that updates a sales summary table:

BEGIN
DBMS_SCHEDULER.CREATE_JOB (
   job_name           =>  'update_sales',
   job_type           =>  'STORED_PROCEDURE',
   job_action         =>  'OPS.SALES_PKG.UPDATE_SALES_SUMMARY',
   start_date         =>  '28-APR-03 07.00.00 PM Australia/Sydney',
   repeat_interval    =>  'FREQ=DAILY;INTERVAL=2', /* every other day */
   end_date           =>  '20-NOV-04 07.00.00 PM Australia/Sydney',
   job_class          =>  'batch_update_jobs',
   comments           =>  'My new job');
END;
/

You can create a job in another schema by specifying schema.job_name. The creator of a job is, therefore, not necessarily the job owner. The job owner is the user in whose schema the job is created, while the job creator is the user who is creating the job. Jobs are executed with the privileges of the schema in which the job is created. The NLS environment of the job when it runs is that which was present at the time the job was created.

After a job is created, it can be queried using the *_SCHEDULER_JOBS views. Jobs are created disabled by default and need to be enabled to run.

Jobs are set to be automatically dropped by default after they complete. Setting the auto_drop attribute to FALSE causes the job to persist. Note that repeating jobs are not auto-dropped unless the job end date passes, the maximum number of runs (max_runs) is reached, or the maximum number of failures is reached (max_failures).

Setting Job Attributes

You can set job attributes when creating the job, or you can set them after the job is created by using the SET_ATTRIBUTE or SET_JOB_ATTRIBUTES procedures or Enterprise Manager. (Some job attributes can be set only after the job is created.)

When you set the COMMIT_SEMANTICS parameter of a job to TRANSACTIONAL or ABSORB_ERRORS, you can perform multiple operations within the scope of a single transaction.

See Oracle Database PL/SQL Packages and Types Reference for information about the SET_ATTRIBUTE and SET_JOB_ATTRIBUTES procedures and about the various job attributes.

Setting Job Arguments

After creating a job, you may need to set job arguments if:

The inline job action is a stored procedure or other executable that requires arguments
The job references a named program object and you want to override one or more default program arguments
The job references a named program object and one or more of the program arguments were not assigned a default value

To set job arguments, use the SET_JOB_ARGUMENT_VALUE or SET_JOB_ANYDATA_VALUE procedures or Enterprise Manager. SET_JOB_ANYDATA_VALUE is used for complex data types that must be encapsulated in an ANYDATA object.

Note:

The SET_JOB_ARGUMENT_VALUE procedure can be used to set arguments of lightweight jobs but only if the arguments are of type VARCHAR2.

An example of a job that might need arguments is one that starts a reporting program that requires a start date and end date. The following code example sets the end date job argument, which is the second argument expected by the reporting program:

BEGIN
DBMS_SCHEDULER.SET_JOB_ARGUMENT_VALUE (
   job_name                => 'ops_reports',
   argument_position       => 2,
   argument_value          => '12-DEC-03');
END;
/

If you use this procedure on an argument whose value has already been set, it will be overwritten. You can set argument values using either the argument name or the argument position. To use argument name, the job must reference a named program object, and the argument must have been assigned a name in the program object. If a program is inlined, only setting by position is supported. Arguments are not supported for jobs of type plsql_block.

To remove a value that has been set, use the RESET_JOB_ARGUMENT procedure. This procedure can be used for both regular and ANYDATA arguments.

See Oracle Database PL/SQL Packages and Types Reference for information about the SET_JOB_ARGUMENT_VALUE and SET_JOB_ANYDATA_VALUE procedures.

Ways of Creating Jobs

Because the CREATE_JOB procedure is overloaded, there are several different ways of using it. In addition to inlining a job during the job creation, you can also create a job that points to a named program and schedule. This is discussed in the following sections:

Creating Jobs Using a Named Program
Creating Jobs Using a Named Schedule
Creating Jobs Using a Named Program and Schedule

Creating Jobs Using a Named Program

You can create a job by pointing to a named program instead of inlining its action. To create a job using a named program, you specify the value for program_name in the CREATE_JOB procedure when creating the job and do not specify the values for job_type, job_action, and number_of_arguments.

To use an existing program when creating a job, the owner of the job must be the owner of the program or have EXECUTE privileges on it. An example of using the CREATE_JOB procedure with a named program is the following statement, which creates a regular job called my_new_job1:

BEGIN
DBMS_SCHEDULER.CREATE_JOB (
   job_name          =>  'my_new_job1',
   program_name      =>  'my_saved_program', 
   repeat_interval   =>  'FREQ=DAILY;BYHOUR=12',
   comments          =>  'Daily at noon');
END;
/

The following statement creates a lightweight job that uses the program MY_PROG as a job template.

BEGIN
DBMS_SCHEDULER.CREATE_JOB (
   job_name         =>  'my_lightweight_job1',
   program_name     =>  'MY_PROG',
   repeat_interval  =>  'FREQ=DAILY;BY_HOUR=9',
   end_time         =>  '30-APR-07 04.00.00 AM Australia/Sydney',
   job_style        => 'LIGHTWEIGHT',
   comments         => 'New lightweight job based on a program');
END;
/

Creating Jobs Using a Named Schedule

You can also create a job by pointing to a named schedule instead of inlining its schedule. To create a job using a named schedule, you specify the value for schedule_name in the CREATE_JOB procedure when creating the job and do not specify the values for start_date, repeat_interval, and end_date.

You can use any named schedule to create a job because all schedules are created with access to PUBLIC. An example of using the CREATE_JOB procedure with a named schedule is the following statement, which creates a regular job called my_new_job2:

BEGIN
DBMS_SCHEDULER.CREATE_JOB (
   job_name                 =>  'my_new_job2', 
   job_type                 =>  'PLSQL_BLOCK',
   job_action               =>  'BEGIN SALES_PKG.UPDATE_SALES_SUMMARY; END;',
   schedule_name            =>  'my_saved_schedule');
END;
/

Creating Jobs Using a Named Program and Schedule

A job can also be created by pointing to both a named program and schedule. An example of using the CREATE_JOB procedure with a named program and schedule is the following statement, which creates a regular job called my_new_job3 based on the existing program my_saved_program1 and the existing schedule my_saved_schedule1:

BEGIN
DBMS_SCHEDULER.CREATE_JOB (
   job_name            =>  'my_new_job3', 
   program_name        =>  'my_saved_program1', 
   schedule_name       =>  'my_saved_schedule1');
END;
/

The following statement creates a lightweight job that is based on the existing program MY_PROG and the existing schedule MY_SCHED.

BEGIN
DBMS_SCHEDULER.CREATE_JOB (
   job_name            =>  'my_lightweight_job2', 
   program_name        =>  'my_prog', 
   schedule_name       =>  'my_sched',
   job_style           =>  'LIGHTWEIGHT');
END;
/

Creating Remote Ex ternal Jobs

The CREATE JOB and CREATE EXTERNAL JOB privileges are both required for any schema that creates remote external jobs.

To create a remote external job:

Create the job using the CREATE_JOB procedure.
Create a credential using the CREATE_CREDENTIAL procedure of the DBMS_SCHEDULER package.

The following example creates a credential named NICKID, which consists of the username NICK and the password firesign:
```
SQL> EXEC DBMS_SCHEDULER.CREATE_CREDENTIAL('NICKID', 'NICK', 'firesign');
```
Set the credential_name attribute of the job using the SET_ATTRIBUTE procedure.

The job owner must have EXECUTE privileges on the credential or be the owner of the credential.
Set the destination attribute of the job using the SET_ATTRIBUTE procedure.

The attribute must be of the form host:port, where host is the host name or IP address of the remote host, and port is the port on which the Scheduler agent on that host listens. To determine this port number, view the file schagent.conf, which is located in the Scheduler agent home directory on the remote host.
Enable the job using the ENABLE_JOB procedure.

Example 1

The following example creates a remote external job named CLEANLOGS that uses a credential named LOGOWNER. The destination host and port number are app455 and 12345.

BEGIN
DBMS_SCHEDULER.CREATE_JOB(
   job_name             => 'CLEANLOGS',
   job_type             => 'EXECUTABLE',
   job_action           => '/home/logowner/cleanlogs',
   repeat_interval      => 'FREQ=DAILY; BYHOUR=23',
   enabled              => FALSE);
DBMS_SCHEDULER.SET_ATTRIBUTE('CLEANLOGS', 'credential_name', 'LOGOWNER');
DBMS_SCHEDULER.SET_ATTRIBUTE('CLEANLOGS', 'destination', 'app455:12345');
DBMS_SCHEDULER.ENABLE('CLEANLOGS');
END;
/

Example 2

The following example creates the same remote external job for multiple remote hosts. The PL/SQL code includes a loop that iterates over the host names. remote_cred is the name of a credential that is valid on all hosts. The list of destinations is a list of host names and Scheduler agent ports. The executable being run on all hosts is the application /u01/app/ext_backup.

declare
job_prefix varchar2(30) := 'remote_';
job_name varchar2(30);
destinations dbms_utility.lname_array;
begin
 
   destinations(1) := 'host1:1234';
   destinations(2) := 'host2:1234';
   destinations(3) := 'host3:1234';
   destinations(4) := 'host4:1234';
 
  for i in 1..destinations.LAST loop
    job_name := dbms_scheduler.generate_job_name(job_prefix);
    dbms_scheduler.create_job(job_name,
    job_type=>'executable',
    job_action=>'/u01/app/ext_backup',
    number_of_arguments=>0,
    enabled=>false);
 
    dbms_scheduler.set_attribute(job_name,'destination',destinations(i));
    dbms_scheduler.set_attribute(job_name,'credential_name','remote_cred');
    dbms_scheduler.enable(job_name);
  end loop;
end;
/

Example 3

The example illustrates how a remote external job can submit SQL statements to a remote Oracle database. The job action runs a shell script that uses SQL*Plus to submit the statements. The script must reside on the remote host. The script, shown below, starts by setting all environment variables required to run SQL*Plus on Linux.

#!/bin/sh

export ORACLE_HOME=/u01/app/oracle/product/11.1.0/db_1
export ORACLE_SID=orcl
export LD_LIBRARY_PATH=$LD_LIBRARY_PATH:$ORACLE_HOME/lib

$ORACLE_HOME/bin/sqlplus /nolog << EOF
set serveroutput on;
CONNECT scott/tiger;
select * from dual;
EXIT;
EOF

Copyin g Jobs

You copy a job using the COPY_JOB procedure or Enterprise Manager. This call copies all the attributes of the old job to the new job except the new job is created disabled and has another name.

See Oracle Database PL/SQL Packages and Types Reference for detailed information about the COPY_JOB procedure.

Alter ing Jobs

You alter a job using the SET_ATTRIBUTE or SET_JOB_ATTRIBUTES procedures or Enterprise Manager. All jobs can be altered, and, with the exception of the job name, all job attributes can be changed. If there is a running instance of the job when the change is made, it is not affected by the call. The change is only seen in future runs of the job.

In general, you should not alter a job that was automatically created for you by the database. Jobs that were created by the database have the column SYSTEM set to TRUE in job views. The attributes of a job are available in the *_SCHEDULER_JOBS views.

It is perfectly valid for running jobs to alter their own job attributes, however, these changes will not be picked up until the next scheduled run of the job.

See Oracle Database PL/SQL Packages and Types Reference for detailed information about the SET_ATTRIBUTE and SET_JOB_ATTRIBUTES procedures and "Configuring the Scheduler".

Runni ng Jobs

Normally, jobs are executed asynchronously. The user creates a job and the API immediately returns and indicates whether the creation was successful. To find out whether the job succeeded, the user has to query the job table or the job log. While this is the expected behavior, for special cases, the database also allows users to run jobs synchronously.

Running Jobs Asynchronously

You can schedule a job to run asynchronously based on the schedule defined when the job is created. In this case, the job is submitted to the job coordinator and is picked up by the job slaves for execution.

Running Jobs Synchronously

After a job has been created, you can run the job synchronously using the RUN_JOB procedure with the use_current_session argument set to TRUE. In this case, the job will run within the user session that invoked the RUN_JOB call instead of being picked up by the coordinator and being executed by a job slave.

You can use the RUN_JOB procedure to test a job or to run it outside of its specified schedule. Running a job with RUN_JOB with the use_current_session argument set to TRUE does not change the count for failure_count and run_count for the job. The job run will, however, be reflected in the job log. Runtime errors generated by the job are passed back to the invoker of RUN_JOB.

When using RUN_JOB to run a job that points to a chain, use_current_session must be set to FALSE.

Job Run Environment

Jobs are run with the privileges that are granted to the job owner directly or indirectly through default logon roles. External OS roles are not supported. Given sufficient privileges, users can create jobs in other users' schemas. The creator and the owner of the job can, therefore, be different. For example, if user jim has the CREATE ANY JOB privilege and creates a job in the scott schema, then the job will run with the privileges of scott.

The NLS environment of the session in which the job was created is saved and is used when the job is being executed. To alter the NLS environment in which a job runs, a job must be created in a session with different NLS settings.

Capturing Standard Error Output for External Jobs

When a local external job or remote external job writes output to stderr, the first 200 bytes are recorded in the ADDITIONAL_INFO column of the *_SCHEDULER_JOB_RUN_DETAILS views. The information is in the following name/value pair format:

STANDARD_ERROR="text"

Note:

The ADDITIONAL_INFO column can have multiple name/value pairs. The order is indeterminate, so you must parse the field to locate the STANDARD_ERROR name/value pair.

To retrieve the entire standard error text, you can use the DBMS_SCHEDULER.GET_FILE procedure. See Oracle Database PL/SQL Packages and Types Reference for detailed information about this procedure.

Stop ping Jobs

You stop one or more running jobs using the STOP_JOB procedure or Enterprise Manager. STOP_JOB accepts a comma-delimited list of jobs and job classes. If a job class is supplied, all running jobs in the job class are stopped. For example, the following statement stops job job1 and all jobs in the job class dw_jobs.

BEGIN
DBMS_SCHEDULER.STOP_JOB('job1, sys.dw_jobs');
END;
/

All instances of the designated jobs are stopped. After stopping a job, the state of a one-time job is set to STOPPED, and the state of a repeating job is set to SCHEDULED (because the next run of the job is scheduled). In addition, an entry is made in the job log with OPERATION set to 'STOPPED', and ADDITIONAL_INFO set to 'REASON="Stop job called by user: username"'.

By default, the Scheduler tries to gracefully stop a job using an interrupt mechanism. This method gives control back to the slave process, which can collect statistics of the job run. If the force option is set to TRUE, the job is abruptly terminated and certain runtime statistics might not be available for the job run.

If commit_semantics is set to STOP_ON_FIRST_ERROR, then the call returns on the first error and the previous stop operations that were successful are committed to disk. If commit_semantics is set to ABSORB_ERRORS, then the call tries to absorb any errors and attempts to stop the rest of the jobs and commits all the stop operations that were successful. By default, commit_semantics is set to STOP_ON_FIRST_ERROR.

Stopping a job that is running a chain automatically stops all running steps (by calling STOP_JOB with the force option set to TRUE on each step).

See Oracle Database PL/SQL Packages and Types Reference for detailed information about the STOP_JOB procedure.

Caution:

When a job is stopped, only the current transaction is rolled back. This can cause data inconsistency.

Stopping External Jobs

The Scheduler offers implementors of external jobs a mechanism to gracefully clean up after their external jobs when STOP_JOB is called with force set to FALSE. On Unix, this is done by sending a SIGTERM signal to the process launched by the Scheduler agent. The implementor of the external job is expected to trap the SIGTERM in an interrupt handler, clean up whatever work the job has done, and exit. On Windows, STOP_JOB with force set to FALSE is supported only on Windows XP, Windows 2003, and later operating systems. On those platforms, the process launched by the Scheduler agent is a console process. To stop it, the Scheduler sends a CTRL-BREAK to the process. The CTRL_BREAK can be handled by registering a handler with the SetConsoleCtrlHandler() routine.

Droppi ng Jobs

You drop one or more jobs using the DROP_JOB procedure or Enterprise Manager. DROP_JOB accepts a comma-delimited list of jobs and job classes. If a job class is supplied, all jobs in the job class are dropped, although the job class itself is not dropped.

For example, the following statement drops jobs job1 and job3, and all jobs in job classes jobclass1 and jobclass2:

BEGIN
DBMS_SCHEDULER.DROP_JOB ('job1, job3, sys.jobclass1, sys.jobclass2');
END;
/

Dropping a job results in the job being removed from the job table, its metadata being removed, and it no longer being visible in the *_SCHEDULER_JOBS views. Therefore, no more runs of the job will be executed.

If an instance of the job is running at the time of the DROP_JOB call, the call results in an error. You can still drop the job by setting the force option in the call to TRUE. Setting the force option to TRUE first attempts to stop the running job instance by using an interrupt mechanism (by calling STOP_JOB with the force option set to FALSE), and then drops the job.

Alternatively, you can call STOP_JOB to first stop the job and then call DROP_JOB to drop it. If you have the MANAGE SCHEDULER privilege, you can call STOP_JOB with force, if the regular STOP_JOB call failed to stop the job, and then call DROP_JOB.

By default, force is set to FALSE.

If commit_semantics is set to STOP_ON_FIRST_ERROR, then the call returns on the first error and the previous drop operations that were successful are committed to disk. If commit_semantics is set to TRANSACTIONAL and force is set to FALSE, then the call returns on the first error and the previous drop operations before the error are rolled back. If commit_semantics is set to ABSORB_ERRORS, then the call tries to absorb any errors and attempts to drop the rest of the jobs and commits all the drops that were successful. By default, commit_semantics is set to STOP_ON_FIRST_ERROR.

The DROP_JOB_CLASS procedure should be used to drop a job class. See "Dropping Job Classes" for information about how to drop job classes.

See Oracle Database PL/SQL Packages and Types Reference for detailed information about the DROP_JOB procedure.

Disabli ng Jobs

You disable one or more jobs using the DISABLE procedure or Enterprise Manager. A job can also become disabled for other reasons. For example, a job will be disabled when the job class it belongs to is dropped. A job is also disabled if either the program or the schedule that it points to is dropped. Note that if the program or schedule that the job points to is disabled, the job will not be disabled and will therefore result in an error when the Scheduler tries to run the job.

Disabling a job means that, although the metadata of the job is there, it should not run and the job coordinator will not pick up these jobs for processing. When a job is disabled, its state in the job table is changed to disabled.

When a job is disabled with the force option set to FALSE and the job is currently running, an error is returned. When force is set to TRUE, the job is disabled, but the currently running instance is allowed to finish.

If commit_semantics is set to STOP_ON_FIRST_ERROR, then the call returns on the first error and the previous disable operations that were successful are committed to disk. If commit_semantics is set to TRANSACTIONAL and force is set to FALSE, then the call returns on the first error and the previous disable operations before the error are rolled back. If commit_semantics is set to ABSORB_ERRORS, then the call tries to absorb any errors and attempts to disable the rest of the jobs and commits all the disable operations that were successful. By default, commit_semantics is set to STOP_ON_FIRST_ERROR.

You can also disable several jobs in one call by providing a comma-delimited list of job names or job class names to the DISABLE procedure call. For example, the following statement combines jobs with job classes:

BEGIN
DBMS_SCHEDULER.DISABLE('job1, job2, job3, sys.jobclass1, sys.jobclass2');
END;
/

See Oracle Database PL/SQL Packages and Types Reference for detailed information about the DISABLE procedure.

Enab ling Jobs

You enable one or more jobs by using the ENABLE procedure or Enterprise Manager. The effect of using this procedure is that the job will now be picked up by the job coordinator for processing. Jobs are created disabled by default, so you need to enable them before they can run. When a job is enabled, a validity check is performed. If the check fails, the job is not enabled.

If commit_semantics is set to STOP_ON_FIRST_ERROR, then the call returns on the first error and the previous enable operations that were successful are committed to disk. If commit_semantics is set to TRANSACTIONAL, then the call returns on the first error and the previous enable operations before the error are rolled back. If commit_semantics is set to ABSORB_ERRORS, then the call tries to absorb any errors and attempts to enable the rest of the jobs and commits all the enable operations that were successful. By default, commit_semantics is set to STOP_ON_FIRST_ERROR.

You can enable several jobs in one call by providing a comma-delimited list of job names or job class names to the ENABLE procedure call. For example, the following statement combines jobs with job classes:

BEGIN
DBMS_SCHEDULER.ENABLE ('job1, job2, job3, 
   sys.jobclass1, sys.jobclass2, sys.jobclass3');
END;
/

See Oracle Database PL/SQL Packages and Types Reference for detailed information about the ENABLE procedure.