Variable Expression

Variable Expressions (Step Triggers)

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.

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

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 introduced 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 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’.

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 introduced 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’.

8. JSON Path Expression

JSON Path Expression allows you to extract values from JSON and place them into process variables without writing PL/SQL.

This expression type is implemented by the runtime expression engine and can read JSON from three different sources:

• a JSON-typed process variable
• the current task input parameters
• the current task output parameters

The expression itself is a JSON Path string, for example $, $.customer.email, or $.mainOutputs.status.

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

Substitutions Allowed: none

Source Types

JSON Path Expressions use a source type to tell the engine where the JSON document comes from.

• processVariable - source is a JSON-typed process variable
• taskOutput - source is the current step’s output parameters JSON
• taskInput - source is the current step’s input parameters JSON

If no source type is specified, the engine treats it as processVariable.

When processVariable is used, the source field should contain the name of the JSON process variable to read from.

When taskInput or taskOutput is used, the source JSON is taken from the current step, and no separate source variable name is needed.

Availability Rules

• processVariable can be used in any expression set, provided the source variable contains valid JSON.
• taskOutput is available in after-task and on-event expression sets.
• taskInput is available in after-task expression sets.

Type Conversion Rules

The extracted JSON value is converted to the target process variable type as follows:

• varchar2 - extracted using a scalar JSON value and stored as text
• number - extracted as a JSON scalar and converted to a number
• date - extracted as text and converted using the standard Flows for APEX date format mask YYYY-MM-DD HH24:MI:SS
• timestamp with time zone - extracted as text and converted using the standard Flows for APEX timestamp with time zone format mask YYYY-MM-DD HH24:MI:SS TZR
• clob - scalar values are returned as text; JSON fragments can also be returned
• json - returned as a JSON fragment

For json and clob targets, the engine can return objects and arrays as JSON fragments. For scalar targets, the engine reads a scalar JSON value.

Missing Values and Errors

If the source JSON is null, invalid, or the JSON Path resolves to no value, the target process variable is set to null and a warning is written to the debug log.

If the JSON Path expression itself is invalid, the engine raises an instance error.

Examples

Example 1. Extract an email address from a JSON process variable named ORDER_JSON.

If ORDER_JSON contains:

{
  "customer": {
    "name": "Example Co",
    "email": "info@example.com"
  },
  "orderNo": "ORD-12345"
}

then the JSON Path expression $.customer.email returns info@example.com.

• Variable Name: CUSTOMER_EMAIL
• Variable Type: VARCHAR2
• Expression Type: jsonPath
• Source Type: processVariable
• Source: ORDER_JSON
• Expression: $.customer.email

Example 2. Copy the full task output JSON into a JSON process variable.

• Variable Name: TASK_OUTPUT_JSON
• Variable Type: JSON
• Expression Type: jsonPath
• Source Type: taskOutput
• Expression: $

Example 3. Extract one returned value from task output parameters after a Script Task completes.

• Variable Name: SEARCH_TIMESTAMP
• Variable Type: TIMESTAMP WITH TIME ZONE
• Expression Type: jsonPath
• Source Type: taskOutput
• Expression: $.search_timestamp

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.

For JSON Path Expressions, note the special source availability rules described above. In particular, taskInput is only available in after-task, and taskOutput is available in after-task and on-event.

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.