Oracle APEX provides a powerful low-code Workflow engine to automate business processes easily. As part of the development lifecycle, Workflows and Application may undergo version updates. It’s crucial to understand how active Workflow Instances are managed during these updates to prevent disruptions and ensure seamless business operations.
The Workflow feature makes development easier by allowing developers to trigger Workflows in Oracle APEX applications using Dynamic Actions, Processes, or Automations through the PL/SQL API. Users can create multiple versions of a Workflow with only one version active in production at any time, while another can be kept in development.
Customers often seek modifications or improvements to their Applications or Workflows. Upon finalising development and testing, the updated applications are usually deployed to the Production environment through CI/CD or One-Click (Remote) deployment methods.
During this process, all Active or Running Workflow Instances in the production environment will be automatically Suspended. These instances can be resumed and will conform to the version that was in effect at the time of their initiation, whereas new Workflow executions will utilize the most recent active Workflow version.
Any active Workflow Instance will be suspended upon deployment, regardless of whether modifications were made to the Workflow, the Application, or both.
The PLSQL API can be utilized to restart the Suspended Workflow Instances with the command..
APEX_WORKFLOW.RESUME (
p_instance_id IN NUMBER,
p_activity_static_id IN VARCHAR2 DEFAULT NULL );
Resuming a workflow instance requires users to have either Workflow Administrator or Workflow Initiator permissions. If multiple Workflow instances are suspended due to deployment, it can be time-consuming, as each one must be resumed individually.
Additionally, identifying Workflow Instances that are Suspended as a result of Application deployment, as opposed to those Suspended manually by Users or Administrators, can be a challenging task.
Users can refer the APEX_WORKFLOW_AUDIT table to determine the cause of suspension, as it includes an audit log for all activities within the Workflow for a specific Workflow Instance.
The following table and snippets detail the approaches for assessing if a Workflow is Suspended as a result of Deployment or User actions, facilitating informed choices regarding which Workflow Instances to RESUME via the PLSQL API.
| APEX_WORKFLOW_AUDT (Table Columns) | Suspended by Deployment | Suspended by User |
|---|---|---|
| ACTION_CODE | ACTIVITY_WAITING | SUSPENDED |
| ACTION | Activity Waiting | Suspended |
| LAST_UPDATED_BY | SYSTEM | (User who Suspended the Workflow) |
Workflow Instance Suspended by Deployment
The below screenshot from the APEX_WORKFLOW_AUDIT table shows a Workflow Instance that is Suspended due to deployment where the latest audit record shows ACTION_CODE = ACTIVITY_WAITING
Workflow Instance Suspended by User
The below screenshot from the APEX_WORKFLOW_AUDIT table shows a Workflow Instance that is Suspended by User where the latest audit record shows ACTION_CODE = SUSPENDED
Refer to the below snippet to fetch Workflow Instances Suspended by Deployment
Refer to the below snippet to fetch Workflow Instances Suspended by Users
Furthermore, an example application is available here that displays Workflow Instances that have been suspended by both Deployment and Users. This application enables users to choose to resume an individual Workflow Instance by entering the Workflow Instance ID (of a suspended Workflow), or to resume all Workflow Instances that are suspended either by Deployment or by Users.
NOTE: To Resume Workflow Instances the logged in user should be a Workflow Administrator. On a side note, if you want to use this application to resume workflow instances across applications, create an extra user as a Workflow Administrator and everytime you want to resume all Instances across applications, login with that user and resume it.
To streamline the process and eliminate the need for manual checks on Workflow Instances Suspended due to app deployment, you can implement one of the two methods described below:
- Create an Automation in Oracle APEX that can be scheduled to execute at a designated time either daily or weekly, depending on your needs. This automation will monitor all Workflow Instances that have been suspended due to deployment and will resume them utilizing the PLSQL API.
- Incorporate the PLSQL block designed to retrieve Suspended Workflow Instances caused by deployment and the Resume PLSQL API into your application installation scripts to ensure automatic handling.
You can follow the below blogs to learn more about Oracle APEX Workflows: