Automation - script to update user variable

I’m new to this forum and only somewhat familiar with some of the TOAD features. IE Toad Newbie and SQL Newbie.

I am using Toad for Oracle 12.12.0.39 and am stuck with the if…then…else syntax.

I have already created a User Variable in the script and started with a default value. Now I want to change the value associated with that user variable based on the result of a query (or some other logical option)

Options such as

  • Table exists
  • Rows returned from a query > 0
  • Query result (single row, single column output)
    Would appreciate some insight. I really like the automation designer but the folks I work with do not use it, so they can’t really answer my questions.

thanks in advance

P

You could use an "Actionable query" action for the if.... part. Actionable queries are on the DB Misc tab in the automation designer.

The actionable query returns true/false based on query result. The idea is to fill in the WHERE clause of "Select 'TRUE' from dual where (.....)" to return true or not.

So, with your examples:

**Table Exists: **

select 'TRUE'
from dual
where ((select 1
from dba_tables
where owner = 'SCHEMA'
and table_name = 'TABLE_NAME') = 1)

Rows returned from query > 0

select 'TRUE'
from dual
where ((select count(*) from emp) > 0);

If you want to put query results into a variable, you might look at Query Iterator. That's it's not limited to single row or single column, but it would work to put a query result into a variable.

I have a dropbox folder here with several word docs describing how to do various things in Toad and Oracle. See the one called "Automation Designer - Run a script in a list of schemas from one database" for an example of query iterator.

-John


1 Like

Thank you John. I like the Actionable Query option. It’s seems much simpler. That being said I’ll take the liberty to modify and ask how to write the syntax because I keep getting an error about missing expression.

Step 1: Create table in my own schema (this step works)

CREATE TABLE run_check

AS (

select

case

when to_char(sysdate, 'DY') in ('WED', 'THU', 'FRI', 'SAT', 'SUN')

then 'Y'

else 'N'

end DAILY

, case

when to_char(sysdate, 'DY') = 'MON'

then 'Y'

else 'N'

end WEEKLY

, case

when to_char(sysdate, 'DD') = '08'

then 'Y'

else 'N'

end MONTHLY_1

, case

when to_char(sysdate, 'DD') = '20'

then 'Y'

else 'N'

end MONTHLY_2

from dual -- day of week short text

);

Note: the above will become more complex, but I’m waiting until I can get the flow to work….

Step 2 Actionable Query -- result should be TRUE if a row is returned or FALSE if no rows

select 'TRUE'

from dual

where ((select 1

from RUN_CHECK

where DAILY = 'Y') = 1)

this seems to follow the syntax but I get the error message

Would appreciate your feedback.

Peter

All you have to fill in the edit box is what comes after “WHERE”.

So take out ‘Select ‘TRUE’ from dual where’

AWESOME. Now we’re cookin’ with gas. Thanks a bunch.