Using Loop Dataset Activity

Summary

This tutorial teaches you how to use the Loop Dataset activity to loop through rows in a result set and perform a task on each row.

Using the Loop Dataset activity requires an understanding of how to use variables in Automation. It is recommended that you read the Automation and Variables tutorial prior to reading this tutorial.

In this tutorial you will learn:

How to use the Loop Dataset activity in Automation
How to use the dataset variable as a bind variable in a SQL statement

You will need:

A sample database
A distinct or driving query
A second query in which the value from the driving query is used as a bind variable

Introduction

Basically, the Loop Dataset stores a result set in the dataset variable using a query you provide (the driving query). Then, for each row in the result set, it performs the activities that you assign to the Loop Dataset. You can assign any activities from the Automation Toolbox.

As an example of when to use the Loop Dataset activity, suppose that you have a list of departments and you run a report for each department and then distribute each report to the department manager.

For this tutorial, however, let's assume you want to create a report for each region in the REGION_NAME column. You will use the following driving query to return a list of region names and use this to populate the dataset variable.

SELECT REGION_ID, REGION_NAME FROM REGION

Then, you will instruct the Loop Dataset activity to execute the following SQL statement each time it loops through the driving query's result set stored in the dataset variable.

SELECT ADDRESS.ADDRESS_ID, ADDRESS.CITY, REGION.REGION_NAME
FROM ADDRESS ADDRESS
INNER JOIN REGION REGION ON (ADDRESS.REGION_ID = REGION.REGION_ID)
WHERE (REGION.REGION_NAME = :CURRENT_REGION)

Create Script

To get started, connect to your sample database.
Select Tools | Automation to open a new Automation window.
Click the Loop Dataset activity in the System Activities category of the Toolbox to add it to the Automation design window.

The Loop Dataset activity uses a query as the input and then saves the result set to the dataset variable. Note that this activity does not automatically create an output file.
In the Activity Input tab, enter a SQL statement (or select a SQL file) to use as the driving query. After the script executes, the result set of the driving query is stored in the dataset variable.

To follow along with this tutorial, enter a query similar to the following:

SELECT REGION_ID, REGION_NAME FROM REGION
(Optional) Notice that Toad provides a default name for the dataset variable. To change this name, you can manually edit it, or you can simply change the activity name.
1. Click the Activity Info tab.
2. Enter a new name in the Name field. To follow along, enter RegionsDataset. Click the Activity Input tab. Notice that the dataset variable name changed to match the activity name.
Click to save and run your script.
After the script executes, review the Log to see the steps the script performed. You can see that the script loops through each row in the driving query. No tasks were performed because there are no activities assigned to the loop yet.

Generate a Report for Each Region

The purpose of the script is to create a report (for each region in the REGION_NAME column), so you need to add this task to the loop. You will use the Select to File activity to execute the original query containing the bind variable and generate a report.

Click the Loop Dataset activity in the Automation workflow to select it.
Now click the Select to File activity in the Toolbox to add it to the Loop Dataset activity.
In the Activity Input tab, enter the original query with the bind variable. The name of the bind variable in your query must match the name of the dataset variable. In this tutorial that name is RegionsDataset_SQL. In addition, you want to select the value in the REGION_NAME column of the dataset variable. So use the following as your bind variable: RegionsDataset_SQL.REGION_NAME .

Note: The bind variable must use bind variable syntax.
Select the Excel File button, if not already selected.
Click in the export file Name field. Browse to and select a location for your report. For the name of the report, enter the following: #RegionsDataset_SQL.REGION_NAME#_Report. For more information on this syntax and using variable names, see Using Variables in Automation.
Click to save and run your script.
After the script executes, review the Log to see the steps the script performed. You can see that the script now loops through the driving query, executes the query containing the bind variable and generates a unique report for each region.

By using variables and the Loop Dataset activity, you can generate a batch of reports based off a driving query.

Schedule A Script

See Schedule Your Script to learn how to schedule the Automation script.