|Oracle Workflow Guide|
Part Number A95265-02
Additionally, Workflow Engines can be set up as background tasks to perform activities that are too costly to execute in real time.
The Workflow Engine performs the following services for a client application:
Attention: The Workflow Engine traps errors produced by function activities by setting a savepoint before each function activity. If an activity produces an unhandled exception, the engine performs a rollback to the savepoint, and sets the activity to the ERROR status. For this reason, you should never commit within the PL/SQL procedure of a function activity. The Workflow Engine never issues a commit as it is the responsibility of the calling application to commit.
For environments such as database triggers or distributed transactions that do not allow savepoints, the Workflow Engine automatically traps "Savepoint not allowed" errors and defers the execution of the activity to the background engine.
Note: The Oracle database server release 8i and higher offers autonomous transactions. By embedding the pragma AUTONOMOUS_TRANSACTION in your procedure, you can perform commits and rollbacks independently of the main transaction. Oracle treats this as a separate session; as such, you will not have access to any database changes that were made in the main session but are not yet committed. Consequently, you are restricted from updating workflow-specific data in an autonomous transaction; for instance, you cannot set item attributes. You cannot access this data because the item itself has not yet been committed, and because you may have lock contentions with the main session.
Oracle Workflow will not support autonomous commits in any procedure it calls directly. If you need to perform commits, then embed your SQL in a subprocedure and declare it as an autonomous block. This subprocedure must be capable of being rerun. Additionally, note that Oracle Workflow handles errors by rolling back the entire procedure and setting its status to ERROR. Database updates performed by autonomous commits cannot be rolled back, so you will need to write your own compensatory logic for error handling. For more information, see: Autonomous Transactions, Oracle Database Concepts.