Toad Data Point Automation Series Blog #13 – Variables in exception reporting

Not all reports are repetitive. Perhaps you have a situation where you only want to produce a report when a set of circumstances arise.

Here is a simple example. Clerks enter orders and at the end of the day submit the batch of orders for processing. When they submit a batch a date is entered into a column. If they forgot to submit a batch of orders a manager would want to know this and resolve quickly. A select statement looking for null values in this column would give you one or more rows when this circumstance occurred. So rows from this query mean there is an exception and we need to do something.

So how could we automate this query and only send an email to the manager when a clerk did not submit their orders? To accomplish this task we are going to use the Row Count variable and If Condition Activity.

�

What’s the Row Count Variable?

 In an earlier video we noted that a variable is a symbol or name that stands for a value. Any of the activities that execute a select statement have a built in automation variable called Row Count. (That would be these 4 automation activities; Select to File, Execute Script, Toad Report, and Toad Pivot Grid.)

To use this variable just enter a name or you can use the default name. When the activity executes your Sql it will set the number of rows returned. You can see this in the log file and use this for debugging purposes.

But we want to use the row count variable to control whether we send out an exception email. To do this, add an If Condition activity. This is located in the System Activities area of your Tool box. Here we want to add an expression that will evaluate to true. The expression we want is “#File_1_RCOUNT #>  0”. This means when the row count is greater than zero, do something. Remember to add the “#’ before and after the variable name to dereference the value.

Now you can add your activities that you want executed when this evaluates to true. In this case I will send an email. I like to add the row count value in the content of the email and also the exception report as an attachment.

If you want you can add another expression and activities. Note: The other branch is not an “If…Else” branch. It does not fall through to the other side. You must put in another expression that will evaluate to true in order to execute any child activities.

So there you have basic exception reporting. Very useful in the right use cases.