Automate Exception Reporting

Summary

This tutorial demonstrates how to use Automation to identify and report special circumstances (exceptions). In this tutorial you will use the presence of a NULL value in a column to trigger exception reporting activities.

In this tutorial you will learn:

How to use the Row Count Variable
How to trigger script activities based on the value in the Row Count variable
How to use the IF Condition activity in Automation

You will need:

A sample database
A query to select rows containing a null value in one column

Introduction

Not all reports are repetitive. You might have a situation in which you want to generate a report only when a set of special circumstances arise. To demonstrate this, assume for this tutorial that you work for a company where orders are entered into a database every day. If the clerk entering the order forgets to submit the order, it is your job to find this mistake (or exception) and report it. An order that was not submitted will have a NULL value in the DATE_SUBMITTED column.

To follow along with this tutorial, create a table that includes a date column named DATE_SUBMITTED. Add some data, and be sure to include a few null values in the DATE_SUBMITTED column.

Create Script

To get started, connect to your sample database.
Select Tools | Automation to open a new Automation window.
Click the Select to File activity to add it to the Automation design window.
In the Activity Input tab, enter a query that selects the rows that contain a NULL value in the DATE_SUBMITTED column.
In the RowCount variable name field, enter a name for this variable. To follow along, enter the name SubmitDate_Exceptions.

The Row Count variable is a built-in variable in Automation. After execution of a query, Toad identifies the number of rows returned and assigns this value to this variable.

In this tutorial, after execution of the query, the Row Count variable value will be equal to the number of rows with a NULL value in the DATE_SUBMITTED column (i.e., the number of orders that were not submitted).

Tip: The Row Count variable can be used in the Select to File, Execute Script, Toad Report, and Toad Pivot Grid activities.
In the Export File Type field, select the Excel File button.
Click in the Name field to select a location and enter a name for your Excel file.
Click to save and run your script.
After the script executes, review the Log. There is a message in the Log that specifies the value of the Row Count variable.

Now click the output file hyperlink to view the output file. The number of rows in the output file should match the value of the Row Count Variable.

Add If..Condition Activity

If, in the previous example, all orders that were entered were also correctly submitted, then the Row Count variable would be assigned a value of 0. So you can see how you can use this variable value to determine when to send an exception report (i.e., when the variable value > 0).

Return to your script and click the If..Condition activity to add it to the Automation workflow just after the Select to File activity. You will use this activity to instruct the script to perform a task when a certain condition is true.
In the design window, click the left branch of the If Condition activity to select it.
Now, build an expression to trigger a task when the expression evaluates to true.
1. In the Activity Input tab, click Variables in the left pane to display the script's variables in the right-pane.
2. Double-click the Row Count variable (#SubmitDate_Exceptions#) to add it to the expression text box.
3. Complete the expression to read "#SubmitDate_Exceptions# > 0"
4. Click Test to test the expression. If you previously ran the script and rows were returned, the expression evaluates to True.
Now add a task to perform when the expression evaluates to true (i.e., the variable value is greater than 0).
1. Drag the Send Email activity from the Toolbox to the design window and drop it in the left branch of the If Condition.
2. With the Send Email activity selected, enter a subject line. Then, in the Body text box, compose a message that includes your Row Count variable, and make sure to surround the variable name with the hash symbol (#) as in the following example.
  Today's order entry produced the following number of exceptions: #SubmitDate_Exceptions#. Please see the attached Exception Report.
3. To attach the Exception Report to the email, click Add Attachments and select the output file generated by the Select to File activity.
In this tutorial, you are not using the right branch of the If Condition. Right-click the right branch and select Delete to delete it.

Note: You can add an expression and task to the right branch, if necessary. However, please note that the If Condition is not an IF ELSE statement. Multiple branches can be true. And each branch must have an expression. Also, if you would like to add a branch to the If Condition, right-click it and select Add Branch.
Click to save and run your script.
After the script executes, review the Log. There is a message in the Log that indicates whether the expression evaluated to True or False.
To further test the If Condition, make a note of your Row Count variable value from the Log. Select the If Condition branch (not the Send Email activity) and modify the expression by replacing "0" with the value of your Row Count variable.
Click to save and run your script.
After the script executes, review the Log. There should be a message in the Log that indicates that the expression evaluates to False and the child activities were not executed.

Exception reporting can be very useful, and Automation can make it easy and worry-free.

What is an Expression?

In the If Condition activity, you use an expression to determine if a certain condition or set of conditions exist. But what is an expression?

An expression is a statement (consisting of values, variables, operators, and functions) that returns (or evaluates to) a value. The value can be logical, numerical, or a string.

Schedule A Script

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