Basic Farmer process specification in Oracle DB

Basic process specification includes specification of mandatory objects: view or AQ queue and processing procedure.

Dataset representation

Process View

In Farmer BPS model in Oracle dataset definition is a view. There are following constraints on process view:

  • view has distinctive column ID$ of number or integer type
  • values in column ID$ are unique
  • column ID$ is indexed for range scanning
  • view has column OBJECT_ID$ of number or integer type
  • column OBJECT_ID$ is indexed for range scanning
  • view is sorted in OBJECT_ID$ ASC, ID$ ASC order
There is another semantic constraint which is necessary for multi-threading execution:
  • records with different value in OBJECT_ID$ column can be processed simultaneously
Columns ID$ and OBJECT_ID$ are called driving columns.

Process Queue

In case of dynamic dataset process dataset is defined by Oracle type which payload in Oracle AQ queue. All payloads types are owner by Farmer internal user and all are subtypes under special type FARMER_ROW also owned by Farmer internal user.

instantiable not final

Due to this fact following constraints are still appropriable for payload type:
  • type has attribute ID$ of number or integer type
  • type has attribute OBJECT_ID$ of number or integer type
Also the semantic constraint is necessary for multi-threading execution:
  • objects different value in OBJECT_ID$ attribute can be processed simultaneously
Attributes ID$ and OBJECT_ID$ are called driving attributes.

Operation representation

In Farmer BPS model in Oracle dataset processing definition is a PL/SQL package. Process package has to meet following specification:

  • define a type T_ROWTYPE which is subtype of view process rowtype or AQ payload type
  • define procedure ONE with at least one argument of T_ROWTYPE
  • any database transaction management in one procedure or its call subtree has to be done in autonomously mode.
Good practice is not to reference to driving columns in procedure one implementation. Sources of ersatzes of mandatory objects are shown is Appendix A.

Optional interface

Besides mandatory interface, process implementation in Farmer BPS model may over-implement following callbacks. By default, implementation of this callbacks is empty.


Application Application name
Name Job name.
Type (data source) View or Queue.
Start date Start date of the job (as in Oracle scheduler).
Repeat interval Repeat interval (as in Oracle scheduler).
Description Job description.
Instances Number of threads (Oracle sessions) the job is executed.
Batch amount Max amount of batches that jobs is processing in one execution.
Name of dataset partitioning Partitioned dataset used by job
Default mode Default execution mode.
Permanency Flag if job session will not log out in case of no records to process.
Sleeping interval Time that process sleeps on waiting for record to process in permanent job.
Instance interval Interval in execution start between subsequent threads in multi-threading runs.
Incrementality Flag, if process is incremental.


Farmer Jobs Runtime Environment

Farmer BPS provides running environment for process execution. Following PL/SQL objects under corresponding synonyms.

Package LOGGER

Package logger provides interface to logging module in Farmer BPS. Interface consist of:

  • type tt_env declaration. tt_env is used to pass some information about logging context to logging module
  • family of procedures to log messages on various level
  • some supplemental utility procedures


Procedure PUT_INTO_QUEUE applies only to AQ based processes.
PUT_INTO_QUEUE takes two parameters: full name of process and object typed FARMER_ROW type. PUT_INTO_QUEUE enqueues message with given object as payload to AQ queue associated to the given process.
PUT_INTO_QUEUE call is by default implemented in process package as put procedure for queue processes.

Procedure WRAPPER

Procedure WRAPPER is a handler to main procedure of Farmer Batch Process Server. It takes three parameters to identify process in Farmer process registry: name of process, name of process application, name of implementing Oracle user.
WRAPPER call is by default implemented in process package as run procedure. Declaration run procedure in process package specification is not obligatory. The one and only result of such declaration will be locking the package object during execution due to run presence on call stack.


FARMER_ENV package provides access to runtime interface of Farmer BPS.
Farmer intereface consist of following:

  • read only access to execution validity range constant parameters of processes or process instance execution
  • read only access to collections of driving identifiers ID$, OBJECT_ID$ for batch that is currently processing; this data has batch range validity and are provided between init_batch and finalize_batch callbacks including both.
  • procedure that raise fatal_one_exception – exception which stops execution of process thread
Technical details of runtime environment objects are to be found in appendix B.


Farmer BPS Aspects

Dataset partitioning

Data set partition is mechanism that allows to precalculate partition into batches a processed dataset. Dataset partitioning is a sequence separated intervals (batches) in form pairs of start and end points.
Using dataset partitioning is not a formal requirement for jobs based on view dataset definition, although using partitioning is very strongly recommended for single-thread jobs and practically indispensable for multi-thread jobs. 
To maximize positive impact of using dataset partitions should gives most balanced distribution of records amount among precalculated batches. 
Dataset partitioning is calculated on pointed integer or integer number column from selectable database object i.e. table or view. Identifiers in dataset partitioning corresponds to identifiers in OBJECT_ID$ driving column in process view.

1 Iteration of processed set of data

Farmer BPS provides dataset iteration functionality. Iteration is executed on two levels - final dataset is divided into batches. Farmer iterates over batches and over elements in current batch. Such approach result that in extremely, but rare cases using of loop statement in implementation of process is not needed.

Exception and transaction management policy

Farmer jobs may be executed in two modes:

Batch mode

Batch mode indicates that database transaction is committed every batch size records. 
When process is running in batch mode and an exception is thrown from one procedure the rollback operation is performed and current batch starts to be processed in one-by-one mode. In renewed one-by-one processing operation one for erroneous record will probably (but not for sure) throw an exception again.

Illustration 1: Exception and transaction management policy in Farmer BPS

All other records from current batch will be also processed. Subsequent batches are performed according to default execution mode i.e. batch mode.One-by-one mode
One-by-one mode indicates that database transaction is committed every single record. One-by-one is recommended when database transactions are distributed on over many databases or when transactional and non-transactional operations are mixed. Queue processes are always executed in one-by-one mode.

Error resistance

Above description shows error resistance of processes executed in Farmer BPS. Moreover Farmer provides dedicated exception to complete error resistance mechanism.
When from one execution fatal_one_exception is thrown processing is stopped and the execution thread ends.

Progress control mechanism

Stopping and resuming of executing process

Farmer BPS provides mechanism for gentle stopping execution of running process. No transaction is rolled back during such stopping. To stop a running process you only need to change status of process. For view based processes current values of driving identifiers are written down and next run will be resumption of interrupted one.


Farmer BPS provide API for logging functionality. Logs are ordered into following types:

ADMIN Actions performed by server administrator.
SYSTEM Major Farmer BPS runtime events.
FATAL Severe errors that cause premature termination.
ERROR Other runtime errors or unexpected conditions. Expect these to be immediately visible on a status console.
WARNING Use of deprecated APIs, poor use of API, 'almost' errors, other runtime situations that are undesirable or unexpected, but not necessarily "wrong".
INFO Interesting runtime events (startup/shutdown).
TRACE Detailed information on the flow through the system.
CALL Detailed log of subroutine call in form of PL/SQL block.

Log record contains following information:

ID Log record id.
MODULE Module which inserted log.
LOG_DATE Date of log.
OBJECT_ID Object id that log is related to.
LOG_TYPE Type of log.
TEXT Content of log.
SID SID of logging oracle session.
JOB_ID Id of logging jobs
JOB_RUN_ID Id of logging job run.
JOB_INSTANCE Id of logging job thread.

Performance diagnostic and optimization

Farmer BPS collects performance diagnostic information. For first or each execution thread (Oracle sessions) are collected:

  • Oracle wait events statistics based on content v$session_event on the end of session activity
  • Oracle SQL trace/tkprof statistics with waits and binds options
  • Oracle dbms_profiler statistics

Performance measures and scalability

Farmer BPS collects following information about of absolute performance and scalability opportunities:

  • absolute performance given in average amount of records processed in one second ; this value is calculated for each execution thread or for whole execution of process
  • time of utilization of CPU in seconds; this value is calculated for each execution thread or for whole execution of process
  • load balance between execution thread given in numbers of records processed by execution thread

Scott Tiger S.A. © 2011