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.
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.
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