Introduction
Have you often wondered, as you’re using Toad, and frequently running your schema compares, executing multiple scripts, generating your reports or even saving table data to files – is there a quicker way to do this?
 
I’m sure you have, which is why Toad’s task automation feature can save you hours, enabling you accomplish your routine tasks faster leaving you to concentrate on other things.
 
First introduced three years ago as Action Palette in Toad version 9.1, the current Automation Designer in Toad 10.6 has come a long way in terms of what can be automated and how it integrates with the rest of Toad.
 
Automation Designer (All Toad Editions)
 
Automation Designer is a way to dramatically improve your productivity by saving the steps you normally take in Toad and persisting them for re-use at any time.
 
The easiest way to create an automated task is – just use ToadJ. There are many places in Toad which enable automation to happen. A simple example is Export Dataset.
 


Saving a task for re-use in Toad

When the Export Data dialog opens, notice the camera icon in lower left corner  . This is the Save/Load Window Snapshot button and you find this button in lots of other windows in Toad such as Compare Schemas, Compare Databases, FTP, DB Health Check, email, TNS Ping and many others.
 
The button to the right of this  enables you to both save and schedule your task and invokes the Windows Task Scheduler.
 
Complete all the necessary options in the Export Data dialog and push the  button. A window opens which asks you for the new name of the Toad App you wish to save your task to (or an existing one if you have one) and the name of the Action itself.

Your task is now saved in Toad’s Automation Designer as an Action and can be re-used simply by selecting it and clicking the run  button. The Run Status column shows a successful completion and the Excel file you requested will be located where you had specified in the Export Data dialog.
 
Running a single Action in Automation Designer
 
To enable you to take more advantage of this feature and better capitalize on opportunities to automate your Toad tasks in the future, an advisor message now appears in Toad 10.6 whenever you use a window which is “Actionable” to remind you that, should you choose to do so, you can simply select and re-execute a stored task.
 
Automating tasks in Toad
 
Actions can be grouped together (called an App), to create a repeatable workflow which can be run or scheduled, within Automation Designer. The example below also incorporates conditional logic, in this case an If…Then…Else loop, to provide some intelligence into the process.
 
Here we are first performing a TNS Ping on a database server to see if it’s operational. If it is, we then run a series of sequential tasks ranging from a data export through to a schema compare (remember, all the information necessary to run these steps is already contained in each Action). We then email a list of recipients with the output from these tasks, achieved with what’s called a List Iterator which contains a list of email addresses before completing.
 
If the server is down, we send a different email to those same recipients notifying them that their reports will be delayed. To run an App, use the  button.
 

Grouping routine tasks in Toad using Automation Designer
 
In order to illustrate Automation Designer being used in a real-world situation, here’s an example of how a Toad customer, based in the US, used Automation Designer to automate a previously manual process.
 
They needed their developers to provide scripts for the DBAs to run in their production server since the developers don’t have access. The DBAs would need to run a bunch of scripts and return the outputs to the developers. This is a task they do on a daily basis. Sometimes they need to run the same scripts on multiple instances.
 
The DBAs wanted to create a Toad App which would take bunch of SQL statements, indicate on what instances to run, have Toad gather all the result sets, and send an email with all the result sets as separate attachments.
 
The App I created below called “Multiple Scripts”, fulfils this objective and, as you can see, it contains 2 Actions.
 
The first is called “Execute Script 1” which I have directed to a folder (using the “Add dir” button) called “App Demo” containing 3 SQL scripts.
 
The second is called “Email 1” which contains the email information for the recipients (the developers in this case) and an attachment named by using the “Add dir” button again which points to the same folder where the output from the script executions will be saved.
 
As the App runs, it will execute each SQL script in turn and direct each output to the same location. It will then send the email with the outputs from the 3 script executions attached.
 
In order that the DBA can run the scripts against the correct databases, the App can be executed against multiple connections by right-clicking and selecting “Run with connections”. The Toad connections window will open and multiple connections selected. The App will then run once for each connection selected.
 

An example of using Automation Designer to automate real time-saving tasks
 
If the DBAs wanted to really automate this process, they could easily create a parameter file which will call the Toad.exe at a pre-determined time by using the Windows Task Scheduler, run the selected App within Automation Designer and pass whichever database connection they wanted to use as a series of arguments. This is useful if they needed to schedule this process.
 
I have saved the above Toad App for you to download from Multiple Scripts.txt. Simply download the App (txt file) to a known location on your desktop. Open Toad and launch Automation Designer. Click the “Import App from File” button  to import the App with its Actions into your window. You will have to change some of the properties (right click the Action and select Properties) so that this App works in your environment or you can simply read it so you get the idea.
 
There are also two sample Apps available now in Toad to help you get started.