How do I add a conditional to an automation script?

Toad Data Point
1

I have set up an automation script to email me the result of a select query.

The select query is a simple one of the form: Select Count(*) From Table

I’d like to set up the script such that I get an email ONLY if the result is greater than zero. Basically, I want to receive an alert if the query returns a non-zero result.

There do not seem to be any options within the UI to set up a conditional of this type. I suspect the Toad Automation Script file could be manually edited to contain this kind of conditional - but I’m not sure how to go about it.

Can anyone offer advice?

2

We currently do not have any if type of logic in Automation. There is a work around in the 2.0 Beta. The work around will not work in any of the current GA release. (Sorry)

Work around: Write a select or block of code that throws an error on your condition. Use the Email on Error feature of 2.0 Automation.

Sample SQL that does this:

– SQL only solution (database independent)
select 1 /
(case when a.num_rows > 0 then 0 else 1 end)
from (select count (*) as num_rows from jkilchoer.mytable) a ;

– Oracle anonymous PL/SQL block
declare
x pls_integer ;
begin
select count (*) into x from jkilchoer.mytable ;
if x > 0
then
raise_application_error (-20001, ‘Table has rows! OMG :(’) ;
end if ;
end ;
/
Hope that helps. Debbie