Oracle Scheduler Interface (OUAF_BATCH)
The Oracle Scheduler Interface is a PL/SQL based package OUAF_BATCH which contains several procedures to initiate batch processes, cancel running batch processes and manage the parameters for the interface.
Submit_Job - Submit a Job or Thread
The Submit_Job procedure submits a batch job and waits for it to complete or fail. It returns COMPLETED or FAILED state to the Oracle Scheduler when used in a Job or Program object.
Column Name
Data Type
Req
Description
batch_code
Text
■
Valid batch control
user_id
Text
■
Valid product user defined in user object. The email address specified on the user object is used to send any configured notifications. Value can be inherited from configured parameters.
thread_count
Number
 
Override for thread count on batch control.
business_date
Date
 
Process date to be used for batch job. Value can be inherited from configured parameters. Value can be set using the to_date function. If no value is provided, the current date will be used. The format of the date must be in a recognizable format. For example, DD-MMM-YYYY is supported. It is recommended to use the to_date function to avoid confusion.
max_timeout_mins
Number
 
Maximum timeout, in minutes, for transactions to avoid ORA-01555 Snapshot too old errors. Default is 15.
rerun_number
Number
 
Used for rerunning specific extracts. Default is: 0 (zero) for current extract.
thread_pool
Text
 
Valid Threadpool name to execute this batch job. This corresponds to the DIST-THD-POOL parameter. If not provided at all, the value of DEFAULT is assumed.
min_run_status
Number
 
Minimum Run Status that will cause this procedure to exit. The default is RUN_STAT_COMPLETED (40). Other values supported:
• 10 - RUN_STAT_PENDING
• 20 - RUN_STAT_IN_PROGRESS
• 30 - RUN_STAT_ERROR
• 40 - RUN_STAT_COMPLETED
raise_error
Boolean
 
If the min_run_status is set a value less than 40, this parameter is set to true and the job status matches the value of the min_run_status then raise an application error. This allows the chain to support conditional branches or stop chains on conditions. Default is false.
thread_notifications
Boolean
 
Whether an error in any thread should trigger a notification event. Default is true. See Notification Options for more information.
single_submitter
Boolean
 
Whether multiple threads should be submitted as separate jobs. Set this to false to enable separate job submissions for each thread.
soft_parm_map
Array
 
Associate array of soft parameters. Refer to Specifying Job Parameters for more details.
soft_parm_list
Array
 
Table array of soft parameters. Refer to Specifying Job Parameters for more details.
Example command lines:
begin
OUAF_BATCH.Submit_Job(
batch_code => 'F1-SYNRQ',
user_id => 'FRED01',
thread_count => 4,
business_date => '01-JUN-15',
max_timeout_mins => 15,
rerun_number => 0,
thread_pool => 'DEFAULT',
min_run_status => OUAF_BATCH.RUN_STAT_COMPLETED,
raise_error => true,
thread_notifications => true,
soft_parm_list => OUAF_BATCH.parm_list_t('someParm','someValue'));
end;
 
begin
OUAF_BATCH.Submit_Job(batch_code => 'F1-SYNRQ', user_id => 'FRED01');
end;
 
begin
OUAF_BATCH.Submit_Job(batch_code => 'F1-SYNRQ',
user_id => 'FRED01',
business_date => to_date('2016-01-01','YYYY-MM-DD'),
thread_count => 1);
end;
 
begin
OUAF_BATCH.Submit_Job(batch_code => 'F1-SYNRQ',
user_id => 'FRED01',
thread_pool => 'POOL01');
end;
 
begin
OUAF_BATCH.Submit_Job(
batch_code => 'F1-SYNRQ',
user_id => 'FRED01',
soft_parm_list =>
OUAF_BATCH.parm_list_t('maintenanceObject','F1-SYNC REQ',
'isRestrictedByBatchCode','true'));
end;
 
begin
OUAF_BATCH.Submit_Job(batch_code => 'F1-SYNRQ',
user_id => 'FRED01',
business_date => to_date('2015-06-01',’YYYY-MM-DD'));
end;
Restart_Thread - Restart a Failed Thread/Job
If a batch job or a thread of a job has failed and needs to be restarted the OUAF_BATCH includes an operation to restart the thread.
The Restart_Thread procedure uses the same parameters of the Submit_Job procedure with an additional parameter:
Column Name
Data Type
Req
Description
thread_number
Number
■
Thread Number to restart. For single threaded jobs, this should be set to 1.
The parameters can match the original execution or be altered to change the behavior of the individual thread.
Note: The thread_count should match the original to ensure all data is processed correctly.
The major difference between Submit_Job procedure and Restart_Thread procedure is that Restart_Thread procedure does not wait for the thread to complete. It simply ends after submitting the thread. Therefore, it is not recommended to be used in a schedule in Oracle Scheduler.
Examples of the command are as follows:
begin
OUAF_BATCH.Restart_Thread(batch_code => 'F1-SYNRQ',
thread_number => 2,
user_id => 'FRED01',
thread_count => 4,
business_date => '01-JUN-15',
max_timeout_mins => 15,
rerun_number => 0,
thread_pool => 'POOL01');
end;
Cancel_Job - Cancel a Running Job
Note: This technique is an alternative to the technique outlined in Batch JMX Reference.
Note: The dbms_scheduler.stop_job procedure is recommended to stop a scheduler job.
A job that is executing can be stopped using the Cancel_Job procedure. The procedure can use one of the following identifiers to cancel the running job:
Column Name
Data Type
Description
batch_code
Text
Valid batch control
job_id
Number
Allocated job identifier from batch daemon (on ci_batch_job table).
For example:
begin
OUAF_BATCH.Cancel_Job(job_id => '00000000000123’,
batch_code => 'F1-SYNRQ');
end;
 
begin
OUAF_BATCH.Cancel_Job(job_id => '00000000000123’);
end;
 
begin
OUAF_BATCH.Cancel_Job(batch_code => 'F1-SYNRQ');
end;
 
The Cancel_Job procedure asynchronously changes the running Submit_Job status to canceled state and terminates. The Submit_Job instance continually monitors the status and will take appropriate action to cancel all the running threads and end with a cancellation error once all the threads have ended.
Refer to Cancellation for more details.
Set_Option - Set a Global or Job Option
The Set_Option procedure is used to set global or job specific options for the scheduler interface. Any option specified using this procedure is subject to the Order of Preference for parameters.
The Set_Option procedure uses the following command format:
begin
OUAF_BATCH.Set_Option(scope => '<scope>', name => '<parmname>', value => '<parmvalue>');
end;
 
<scope>
Scope of the parameter. The valid values are GLOBAL for global settings or a valid Batch Control for a specific Batch Control. Settings at the Batch Control level override the GLOBAL scope.
<parmname>
The parameter name from the list below. This setting can be overridden on the command line.
<parmvalue>
The value of the parameter designated by <parmname> to be used for the scope designated by <scope>.
The parameters supported by this option are listed below:
Parameter Name
Description
business_date
The default business date is the database’s current system date, but this can provide an alternative default. It is typically useful in the case of jobs that run past midnight to make it maintain the same date for all the submissions. The date format is ISO format: YYYY-MM-DD.
user_id
The user id is required for every submission but to avoid having to specify it on every Submit_Job call, this will define a default either globally or for specific batch codes. The user must be defined as a user object within the product.
poll_seconds
By default, the batch tables are checked every 1 second for updates. This provides for very responsive job handling, but if it is too taxing on the database, the frequency can be modified with this setting. The value must be between 1 and 60.
notify_job_name
Oracle Scheduler job OUAF_NOTIFY is the default thread notification job. That is defined out of the box to send emails for every failed thread. If that behavior is undesired, a custom thread notification, Oracle Scheduler job can be created, and it defined as the default handler with this option. The new notify Oracle Scheduler job must exist.
thread_notifications
Set to false to suppress thread notifications for failed threads. The default is true.
thread_pool
The default threadpool to use. The installation default threadpool is DEFAULT.
discard_queue
Set to true to delete all unused online job queue entries (CI_BATCH_JOB rows) created by OUAF_BATCH. The default is false. OUAF_BATCH uses individual CI_BATCH_JOB rows for the threads so it may become quite voluminous, and the job queue entries are typically not useful once a thread has started executing as the Batch Run Tree entries are used to track the job. This option can be used to clean up the unused entries.
debug
Set to true to see internally generated DBMS_OUTPUT debug messages. The default is false. Used for development purposes.
single_submitter
Set to true to submit a separate job for each thread of batch job. Default behavior is to submit a single job for multiple threads of batch job.
For example:
Begin
OUAF_BATCH.Set_Option(scope => 'GLOBAL', name => 'user_id', value => 'FRED01');
end
 
To remove the option, use the Unset Option (see Unset_Option - Reset a Global or Job Option) procedure.
Unset_Option - Reset a Global or Job Option
By using the Set_Option feature sets the global or job specific parameters for the scheduler it is possible to also remove the settings using the Unset_Option feature on the parameters covered by the Set_Option feature. The format of the command is:
begin
OUAF_BATCH.Unset_Option(scope => '<scope>', name => '<parmname>');
end;
 
<scope>
Scope of the parameter to reset. The valid values are GLOBAL for global settings or a valid Batch Control for a specific Batch Control.
<parmname>
The parameter name to reset. Refer to the Set Option (see Set_Option - Set a Global or Job Option) procedure for the valid list.
For example:
begin
OUAF_BATCH.Unset_Option(scope => 'GLOBAL', name => 'business_date');
end;
Raise_Thread_Error - Raise an Error
Whenever a thread or job fails the Oracle Scheduler Interface informs the product of the failure as well as the scheduler itself. This is handled by the Raise_Thread_Error. This procedure is not directly called by the scheduler itself and is used internally by the interface itself.
Setting Preferences
One of the first steps in configuration is setting the preferences
• The Oracle Scheduler includes several key preferences that need to be configured to ensure a consistent operation of the scheduler. These preferences are documented in the Setting Scheduler Preferences section of the Oracle Scheduler Administration documentation. It is recommended to set the preferences before using any of the functionality in the Oracle Scheduler.
• Once the Oracle Scheduler Interface is installed the GLOBAL options for the interface, should be set if they differ from the recommended defaults. This can be achieved using the Set Option (see Set_Option - Set a Global or Job Option) procedure. At a minimum, it is recommended to set a global user_id to act as a default to avoid individual job issues.
• Optionally, it is possible to set Batch Job specific options using the Batch Control as the key for the Set Option (see Set_Option - Set a Global or Job Option) procedure.
The above facilities set the overall scheduler and interface preferences.
Order of Preference
There are a number of different techniques available for setting the parameters for any job. The available techniques from lowest precedence to highest are as follows:
• Batch Control - Job parameters can be set on the Batch Control record within the user interface under control of authorized users. This is commonly maintained by the business.
• Global Preferences - Some of the parameters from the Batch Control can be specified as a GLOBAL preference using the Set Option (see Set_Option - Set a Global or Job Option) procedure. These preferences, if set, will override any Batch Control settings for the same setting.
• Batch Job Preferences - The Set Option (see Set_Option - Set a Global or Job Option) procedure also supports the setting of parameters using the Batch Control as the key. Any overlapping settings set at this level will override settings on the Batch Control and any GLOBAL preference for the same setting.
• Command Line Options - It is possible to override all settings using command line options on the Submit Job (see Submit_Job - Submit a Job or Thread) and Restart Thread (see Restart_Thread - Restart a Failed Thread/Job) procedures. This is handy for special processing. See Using soft_parm_map and Using soft_parm_list for advice and examples.
It is recommended to set the values in the relevant level and use command line options for special situations or when using dynamic parameters.
Using soft_parm_map
Batch parameters can specified on the command line through the soft_parm_map, which is an associative array. This format can be used in isolation or in association with soft_parm_list (see Using soft_parm_list) when specifying command line parameters. When both are used, the soft_parm_list values are taken in precedence
To use associative arrays, the array must be declared before use in the PL/SQL command.
Any array must be declared as type OUAF_BATCH.parm_map_t.
The format of soft parameter in an associate array is:
Segment
Format
Declaration of array
declare <arrayname> OUAF_BATCH.parm_map_t;
Definition of value
<arrayname>('<parmname>') := '<parmvalue>';
Use of map
soft_parm_map => <arrayname>
Where:
<arrayname>
Name of associative array to be used as the soft_parm_map.
<parmname>
Name of the parameter to be set.
<parmvalue>
Value of parameter.
For example:
declare
my_soft_parms OUAF_BATCH.parm_map_t;
begin
my_soft_parms('maintenanceObject') := 'F1-SYNC REQ';
my_soft_parms('isRestrictedByBatchCode') := 'true';
OUAF_BATCH.Submit_Job(batch_code => 'F1-SYNRQ',
user_id => 'FRED01',
soft_parm_map => my_soft_parms);
end;
 
The my_soft_parms is the soft_parm_map in the above example. The use of associative arrays is a way of supporting dynamic parameters for complex parameters. This technique can be used exclusively or in conjunction with soft_parm_list.
Note: The associative array should not contain common parameters such as business_date, user_id, and so on.
Using soft_parm_list
An alternative to the soft_parm_map (see Using soft_parm_map) is to explicitly specify the parameters as a list of parameter and value pairs. This method is recommended for explicit values for the parameters. The parameter list must be of type OUAF_BATCH.parm_list_t.
The parameters are specified in the following format:
OUAF_BATCH.parm_list_t('<parmname>','<parmvalue>'…)
 
where:
<parmname>
Name of the parameter to be set
<parmvalue>
Value of parameter
For example:
begin
OUAF_BATCH.Submit_Job(
batch_code => 'F1-SYNRQ',
user_id => 'SYSUSER',
soft_parm_list =>
OUAF_BATCH.parm_list_t('maintenanceObject','F1-SYNC REQ',
'isRestrictedByBatchCode','true'));
end;
 
This technique can be used exclusively or in conjunction with soft_parm_map.
Note: The array should not contain common parameters such as business_date, user_id, and so on.