Introduction

Declarative Process Variable Expressions were introduced in Flows for APEX v21.1.

Declarative Process variable expressions allow you to define one or more process variable assignments to be made before and after each instance step is executed. This allows you to set up process variables, record process state, lookup values from the database, and otherwise set and process Process Variables as your instance steps through its Flow model. You can think of these as ‘triggers’ which fire before and after each step in a process, updating process variables as required.

Process Variable Expressions are defined in the Flow Modeler, and processed in the Flow Engine as the Instance progresses.

All Variable Expressions operate within the scope of the subflow causing them to fire.

Note that Call Activity In/Out Variables provide similar functionality to Variable Expressions. See Call Activities page for information about scoping of In/Out Variables.

Expression Types

1. Static

Static assignment allows you to set a variable with a static value supplied in the model.

Process Variable Data-types Supported: varchar2, number, date, timestamp with time zone, json

Substitutions Allowed: The static string can contain Flows for APEX substitution variables, including &F4A$process_id., &F4A$subflow_id., &F4A$<process_var_name>.

Format issues:

  • Static values supplied for number typed variables must be character strings in a format that will convert into an Oracle number field using a standard Oracle to_number type conversion.
  • Static values supplied for date typed variables must be character strings that comply to the Oracle format mask ‘YYYY-MM-DD HH24:MI:SS’.
  • Static values supplied for JSON typed variables must be valid JSON using the Oracle lax definition.

2. Copy of Process Variable

Copy of Process Variable allows you to set a variable to the current contents of another Process Variable.

Process Variable Data-types Supported: varchar2, number, date, clob, timestamp with time zone, json

Substitutions Allowed: none

Format issues: None. Process Variables are copied in-type from the source process variable to the destination process variable.

3. SQL Returning Single Value

Static assignment allows you to set a variable from the result of a SQL query returning one row.

Process Variable Data-types Supported: varchar2, number, date, timestamp with time zone

Substitutions Allowed: The query definition can contain Flows for APEX substitution variables, including &F4A$process_id., &F4A$subflow_id., &F4A$<process_var_name>.

Format issues: The query must return a value of the correct type to match the variable. A query returning no data will result in the variable being set to null.

Example:

  1. Variable King_Job defined as varchar2 with SQL query as
select job 
  from emp 
 where ename = 'KING';

creates a varchar2 variable with content PRESIDENT.

4. SQL Returning Delimited List

SQL Returning Multiple Values allows you to set a variable as a delimited list of multiple values. The delimiter is ‘:’ (colon).

Process Variable Data-types Supported: varchar2 only

Substitutions Allowed: The query definition can contain Flows for APEX substitution variables, including &F4A$process_id., &F4A$subflow_id., &F4A$<process_var_name.

Format issues: The query must return one column containing zero or more rows containing a varchar2 value, which will be concatenated using ‘:’ (colon) separators into a single varchar2 process variable. If the query returns no values, the process variable will be null.

Example:

  1. Variable Sales_staff defined as
select ename 
  from emp 
 where deptno = 30

creates a variable Sales-staff of type varchar2 with content BLAKE:ALLEN:WARD:MARTIN:TURNER:JAMES

7. SQL Returning JSON Array🆕

SQL returning multiple rows of multiple columns, which will be converted to a JSON Array of JSON objects.

Data-types Supported: Any database column type that can be converted into a JSON element - so varchar2, number, timestamp, boolean (Oracle 23ai), etc. Standard JSON conversion rules apply when converting data values into JSON scalar items.

Example:

  1. Variable sales_staff_array defined as
select ename, empno, hiredate
  from emp
 where deptno = 30

creates a JSON array as follows

[ 
  { "ENAME":"JONES" ,"EMPNO":7566 ,"HIREDATE":"1981-04-02T00:00:00Z" } ,
  { "ENAME":"SCOTT" ,"EMPNO":7788 ,"HIREDATE":"1982-12-09T00:00:00Z" } ,
  { "ENAME":"FORD" ,"EMPNO":7902 ,"HIREDATE":"1981-12-03T00:00:00Z" } ,
  { "ENAME":"SMITH" ,"EMPNO":7369 ,"HIREDATE":"1980-12-17T00:00:00Z" } ,
  { "ENAME":"ADAMS" ,"EMPNO":7876 ,"HIREDATE":"1983-01-12T00:00:00Z" } 
]

6. Expression

Expression allows you to set a variable to the result of a PL/SQL expression clause. The PL/SQL expression must return a string of type varchar2. If the variable is a number or a date, this string must convert to an Oracle number or to a date using the required format mask (see below).

Data-types Supported: varchar2, number, date, timestamp with time zone

Substitutions Allowed: Substitution is not allowed. However, flow_globals.process_id and flow_globals.subflow_id are available to use in your expression.

Format issues:

Prior to v23.1, expressions had to return a value of type varchar2, meaning that calculations returning a number, date or timestamp had to return these values after converting them to a varchar2. This functionality continues to be supported for Expressions (Legacy Mode).

Version 23.1 introdued expressions that return values in number, date and timestamp with time zone format – which are much easier and more natural for the develop to work with. We suggest that you use the new, non-Legacy mode expressions for new projects!

If you are working with Expressions (Legacy Mode), note that:

  • values returned for number type variables must be character strings in a format that will convert into an Oracle number field using a standard Oracle to_number conversion.
  • values retured for date type variables must be character strings that strictly comply to the Oracle format mask ‘YYYY-MM-DD HH24:MI:SS’.
  • values returned for timestamp with time zone type variables must be character strings that strictly comply to the Oracle format mask ‘YYYY-MM-DD HH24:MI:SS TZR’.

7. Function Body

Function Body allows you to set a variable to the return value of a PL/SQL function body. The PL/SQL function body must contain a RETURN clause that returns a value of type varchar2. If the variable is a number or a date, this string must convert to an Oracle number or to a date using the required format mask (see below).

Data-types Supported: varchar2, number, date, timestamp with time zone

Substitutions Allowed: Substitution is not allowed. However, flow_globals.process_id and flow_globals.subflow_id are available to use in your expression.

Format issues:

Prior to v23.1, function bodies had to return a value of type varchar2, meaning that calculations returning a number, date or timestamp had to return these values after converting them to a varchar2. This functionality continues to be supported for Function Body (Legacy Mode).

Version 23.1 introdued function bodies that return values in number, date and timestamp with time zone format – which are much easier and more natural for the develop to work with. We suggest that you use the new, non-Legacy mode function bodies for new projects!

If you are working with Function Body (Legacy Mode), note that:

  • values returned for number type variables must be character strings in a format that will convert into an Oracle number field using a standard Oracle to_number conversion.
  • values returned for date type variables must be character strings that strictly comply to the Oracle format mask ‘YYYY-MM-DD HH24:MI:SS’.
  • values returned for timestamp with time zone type variables must be character strings that strictly comply to the Oracle format mask ‘YYYY-MM-DD HH24:MI:SS TZR’.

When do Variable Expressions get Executed?

Variable expressions are, in general, triggered before and after each object in the Flow diagram. Each trigger point can contain a set of zero or more expressions that are evaluated as part of the expression set.

  • BPMN Tasks: ( i.e., bpmn:task, bpmn:scriptTask, bpmn:manualTask, bpmn:serviceTask, bpmn:userTask, bpmn:businessRulesTask, bpmn:sendTask, bpmn:receiveTask), variable expressions can be executed before-task and after-task.
  • BPMN Gateways: ( i.e., bpmn:exclusiveGateway, bpmn:inclusiveGateway, bpmn:parallel Gateway, and bpmn:eventBasedGateway), variable expressions can operate before-split** and after-merge**.
  • BPMN Events: (i.e., bpmn:startEvent, bpmn:endEvent, bpmn:intermediateCatchEvent, bpmn:intermediateThrowEvents, bpmn:boundaryEvents, bpmn:sendEvent, bpmn:receiveEvent), variable expressions can be evaluated on-event. In addition for Timer events, as there can be a long interval between a timer event becoming current and the timer firing, variable expressions can also be triggered when they first become current using the before-event triggering point.

Referencing Process ID and Subflow ID in an Expression

The current Process ID and Subflow ID are made available to you to use inside an expression or a function body as:

  • flow_globals.process_id
  • flow_globals.subflow_id
  • flow_globals.scope

flow_globals.step_key is not available inside a function or expression.

Handling Errors in Variable Expressions

Variable expression evaluation can fail if the expression definition contains an error, if they encounter data that is incorrect or not in the expected format, or for other reasons. If errors occur while processing process variable expressions, the behavior depends on whether the failing expression is in the user’s current step, or on a later step that has been automatically triggered by completing the current step.

  • if the error is in the user’s current step, Flows for APEX will present an error message to the user, and typically not allow the user’s current transaction to complete.
  • if the error is in a step triggered by the user’s current step, for example in a gateway or a scriptTask following on from the user’s current task, the user’s transaction will be allowed to proceed; any following steps that complete successfully will complete, but the step containing the error will be put into error status and will rollback. After an administrator fixes the underlying problem, the failed step can be restarted from the Flow Monitor.
  • if the error occurs when evaluating an expression in the on-event set of a Timer-based event, such as a Timer Start, a Timer Intermediate Catch Event, or a Timer Boundary Event, the step will be put into error status, and will rollback to the point at which the timer fired. After the administrator fixes the underlying problem, the step can be restarted. The restart will be immediate, i.e., the timer is not re-run, and will run the on-event expression set before stepping forward to the next step of the process model.