This tutorial provides several examples of how to create and use variables in Automation scripts.
In this tutorial you will learn:
You will need:
Watch Automation and Variables to see a video presentation of this tutorial.
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. But what is a variable? A variable is a symbolic name that has a value assigned to it, a value that may change.
Variables play an important role in scripts. Variables can be used to represent and store data that may be different each time the script executes. For example, if you include a bind variable in a SQL statement, Toad prompts you for the value of the variable each time you execute the statement. In this way, you can change the variable's value with each execution, which returns a different result set, without changing the SQL statement.
Follow along with this tutorial to learn how variables can be used in Automation. The query examples given in this tutorial are intended to be used with the Toad Sample Database (available with Toad Data Point). If you are using a different sample database, use similar queries written for your database.
Enter the following SQL statement:
Select Format(DatePart('w', Date()),'dddd')
Note: If you are using a database other than the Toad Sample database, enter the equivalent statement that returns the current day of the week for your DBMS.
In the Activity Input tab, enter the following in the Log Message text box:
Value = #Day#
Note: In most cases, surround the variable name with hash symbols (#) when you want to express the value of the variable. In SQL statements, you can use bind variable syntax.
After the script executes, review the Log. The Log comment you added displays the value of the variable.
You can set a variable value and then express that variable value in a file name.
Click in the Name field. Browse to a location to save your report, and enter the following file name:
After the script executes, review the Log. Notice that the script created an Excel file that includes the current day of the week in the file name. If you execute the script tomorrow, the file will include tomorrow's day of the week in the file name.
You can set a variable and then use that variable to filter query results.
To demonstrate this example, modify your query to filter your results using the value of your variable. For example, if you are using the Toad Sample Database, enter the following query:
Select * from Contact
Where Format(DatePart('w', Birth_Date),'dddd') = :Day
This query selects the contacts whose birthdays occurred on the same day of the week as the current variable value. The variable is indicated in the SQL statement by using bind variable syntax, in this case a colon (:) preceding the variable name. When Toad executes the statement, it binds the value of the variable (in this case, Monday) into the SQL statement .
You can set a variable and then use that variable within an email. For example, use the variable in the subject line or body.
Automation variables can make your scripts and outputs extensible and dynamic.
See Schedule Your Script to learn how to schedule the Automation script.