Use System Automation Activities

Run a program with optional command-line arguments. Review the following for additional information:

• Program—Select a .bat or .exe file.

• Arguments—Enter command-line arguments. You can also enter a variable, such as #sleep# in this field.

  Note: If you want to run a macro in Access, enter databasename /x macroname in this field. For example, if you have a macro that beeps and runs in your Northwind database, you would enter Northwind 2007.accdb /x beep.

• Run Directory—Select the directory to change to when running the program.
• Return Code Variable—Enter a name for the return code variable. You can use this variable with the If..Condition activity.
• Verb—Enter verbs used when running the program, such as "Print".
• Wait for program to exit—Select this option to pause if there is an error. Select duration of wait time in minutes.

Note: If the script opens a command-line window, you must manually close the window once the script completes.

Add one or more variables to the script. When you execute the script, Toad replaces the variable with the defined value. With this feature, you can update information that may be different each time you run the script, such as the department name, revenue, fiscal quarter, or product name.

Review the following for additional information:

Value—Select this option to specify a value for the variable or to build a variable expression. Click to build an expression. Build your expression by selecting from the list of variables, operators, and functions. (Click a list name in the left pane to display the items in the right pane. Double-click an item to add it.) Click Test to test the expression.

Notes:

• See Date Formats Used in Automation for a list of date formats that can be used in a variable.
• See the following link for more information on VBScript functions: http://www.w3schools.com/VBscript/vbscript_ref_functions.asp

SQL—Select or enter a variable value, you can enter a SQL statement to use as the variable. If you enter a statement, the value from the first row and column returned in the query is used as the variable value. This is useful if you need a variable with a date.

See Use Variables in Automation for more information.

Note: You can only use one variable per field or file name (a suffix counts as one variable for a file name).

Execute activities conditionally based on the value of a variable. For each condition you can define unique activities, such as connecting to a database, generating files, and sending emails.

To use this activity you must define a variable in the automation script with the Set Variable activity.

This activity can have one or more conditions. To delete a condition, select it and press DELETE. To add a condition, right-click the If..Condition.. activity and select Add Branch. If you have multiple conditions, they do not have to be mutually exclusive. Multiple branches can be true.

• Build condition expression—To define a condition, enter the variable name and expression in the expression field. Build your expression by selecting from the list of variables, operators, and functions. (Click a list name in the left pane to display the items in the right pane. Double-click an item to add it.) Click Test to test the expression. See the following link for more information on VBScript functions: http://www.w3schools.com/VBscript/vbscript_ref_functions.asp

Execute activities while the variable condition is true. The activity ends when the condition becomes false, or when it completes the selected number of loops.

To use this activity you must define a variable in the automation script with the Set Variable activity.

This activity can have one or more conditions. To delete a condition, select it and press DELETE. To add a condition, right-click the If..Condition.. activity and select Add Branch. If you have multiple conditions, they do not have to be mutually exclusive. Multiple branches can be true.

Review the following for additional information:

• Build condition expression—To define a condition, enter the variable name and expression in the field. Build your expression by selecting from the list of variables, operators, and functions. (Click a list name in the left pane to display the items in the right pane. Double-click an item to add it.) Click Test to test the expression. See the following link for more information on VBScript functions: http://www.w3schools.com/VBscript/vbscript_ref_functions.asp
• Loop—Enter the number of times to loop the activity. If the condition never becomes false, Toad ends the activity after this number. The default is 10.

Execute activities on each row of a data table. The data table must be the result set from an Execute Script activity that occurs previously in the automation script. See Use Database Automation Activities for more information.

You can reference a column in the data table by setting the value of an initialized variable to 'DataTableName.ColumnName'. To initialize a variable, your script must have two different Set Variable activities. The first Set Variable activity defines the variable value with a numeric value (such as 0 or 1.53), and the second defines it with the column variable name.

Click here for an example of how to use the Loop dataset activity and initialize a variable.

Assume you want to perform a set of activities on each of your company's products. You add the following activities and values:

• Execute Script—Execute a script that returns product IDs. You enter ProductIDTable in the Results variable field.
• Set Variable—Add activity to give the variable an initial value. You enter ID in the Name field and 0 in the Value field.
• Loop dataset—Loop based on the dataset from the Execute Script activity. You enter ProductIDTable in the Data Table field.
• Set Variable—Add a second variable activity to reference the Product_ID column in the data table. You enter ID in the Name field and ProductIDTable.Product_ID in the Value field. This redefines the values of the ID variable to the values in the Product_ID column.

See Use Variables in Automation for more information on a scenario that uses this activity.

Stops an activity for a set number of seconds before continuing. This is useful, for example, if you are using parallel activities and need to pause one branch while waiting for results or the complete execution of another branch.

Run two or more activities in parallel. After adding a parallel activity to the design pane, you can right-click it to add additional branches for parallel activities.