Using Variables in Automation

Automation variables are a powerful tool that can greatly extend your scripts. Variables can add flexibility and customization. Variables can be used in almost every Automation activity.

How to Create Variables

User-Defined Variables

You can use the Set Variable activity to create user-defined variables. See Use System Automation Activities for more information.

Built-In Variables

Several Automation activities create variables by default. The following built-in variables are created by Automation activities.

RowCount variable— This integer type variable is created by several activities. The value assigned to this variable is the number of rows returned by the query in the activity.
Return code variable— This integer type variable is created by the Run Automation Script and the Run Program activities. The variable value is assigned based on the execution status of the script or program.
Built-in SQL type variables—The Loop Dataset and Execute Script activities create a SQL type variable which is used to store the result set of a query. The Loop Connections activity creates a SQL type variable with only one row containing connection information.

How to Use Variables

Where to Use Variables

The following are some examples of where you can use a variable (built-in or user-defined) in an Automation script.

In the file name of a file exported by an activity.
In the suffix appended to a file name.
In an expression used in the While or If Condition activities.
In a SQL statement as a bind variable for filtering data.
In a SQL statement as a bind variable in an Import or Export template.

How to Use a Variable

To express the value of a variable, surround the variable name with hash symbols (#), for example #myvar#. The following are some examples of how variables might be used.

#myvar#_report.xlsx

Report_#Dept_Name#.xlsx

#File_1_RCOUNT# > 0

It is important to remember that when a variable is expressed, the variable value is substituted as text in place of the variable name (#myvar#). The only exception is when you use a variable as a bind variable in a SQL statement.

To use a variable as a bind variable in a SQL statement, use the following syntax format (regardless of what the address_id column type is):

SELECT * FROM contact WHERE address_id = :myvar

You can also use the Automation variable here as text substitution, but in this case single quotes might be required (because it is a text substitution) depending on the column type in the WHERE clause.

For example, if last_name is a character column, use the following syntax (single quotes are required):

SELECT * FROM contact WHERE last_name = ‘#myvar#’

If address_id is a non-character column, use the following syntax:

SELECT * FROM contact WHERE address_id = #myvar#

How to Use a String Variable in a Comparison Expression

When comparing a string variable to a string value in an expression, always surround the variable name and the string value with single quotes, for example:

'#myvar#' = 'Dog'

About DateTime Type Variables

If you define a variable using the Set Variable activity and specify the variable type as DateTime, you must use a Date function for the value.

About SQL Type Variables

SQL type variables are created in two ways:

You can create user-defined SQL type variables using the Set Variable activity.
A built-in SQL variable is created by the Loop Dataset activity, the Execute Script activity, and the Loop Connection activity.

If you ask just for the variable value of a SQL type variable, Toad returns the value from the first row and column of the result set. However, you can filter by column using this syntax:

<variable name>.<column name>.

For example, Region.Region_Name returns the first value (first row) in the column Region_Name.

Dataset/SQL Variable - Special Format for Filtering

You can use the following syntax format to filter data from the dataset variable (or any SQL type variable):

<dataset variable name>.<filter parameter>

Example: Loop_data_SQL_1.region_id, where region_id is a column name

Bind Variables

You can store global bind variable/value combinations in Toad (see Using Bind Variables). After storing a global bind variable, that variable is available to use in Automation. Your stored global bind variables appear in the list of variables in the expression editor of applicable activities.

Variable Scope

When you are building an Automation script that uses a variable, it is important to understand the variable's scope of visibility. This section describes the concept of variable scope in Automation scripts.

Terms and Definitions

Term	Definition
Container	Let's define a container as: The script itself. An activity that contains other activities (like the Group, While, or Loop Dataset activities). A branch of an activity (for example, a branch in the If Condition activity).
Levels	Suppose that a script is composed of nested containers. Each container is a different level with respect to variable scope. The outermost container is the script itself. Inside the script are activities. The script and its activities in the main workflow comprise the first level. Some activities can contain other activities. These "container activities" are at the second level. Container activities can contain branches. A branch within a container activity is the third level. And so on, until we get to the innermost level.
Container Activity	An activity that contains other activities. The Loop Dataset, Group, If Condition, While, and Parallel activities are container activities.
Set Variable Activity	Use this activity to define a variable.
Set Variable Value Activity	Use this activity to assign a new value to a previously-defined variable.

Scope Rules

Basically, the scope of a variable includes all activities subsequent to the variable-defining activity AND at or below the level at which the variable is defined.

The following are the rules for variable scope.

Use a variable in an expression—You can use a previously-defined variable in an expression in any activity subsequent to the variable-defining activity AND at or below the level at which the variable was defined.
Reassigning a variable value—You can reassign the value of a variable (except a SQL type variable) at any point within the variable's scope. This task is accomplished using the Set Variable Value activity. See Use System Automation Activities for more information.

Once a variable value is reassigned, the new value applies to activities subsequent to the original Set Variable activity AND at or below the level of the original Set Variable activity.

Example: You can use the Set Variable Value to turn a previously-defined variable into a counter (var=var+1) in the Loop Dataset activity.
Define a new variable with the same name—You can redefine a new variable with the same name, but only within an activity one or more levels below the variable-defining activity. For example, if you defined a variable in the main workflow of your script (first level), you can define a new variable with the same name within a container activity (second level). The scope of this variable value is restricted to that container activity.

You cannot define a new variable with the same name at the same level as the original.