Now add a script action. Note the script is in a file – don’t use the “text” option where you just paste the script into the action. After adding the file, you can double-click in the “parameters” column to set parameters. VAR1 is the name of the variable in the first action (it’s hard to see but that’s what I typed in where it is selected in blue above.
My script looks like this. I am using &1 because this is the first parameter. If I had more than one of them, the 2nd would be referenced in the script as &2 , and so on.
Run both actions together by right-clicking the app, like this:
Thank you - that worked great! I was using a query file anyway since my goal was to use the file in several automation scripts, but only change the parameter. Now that you pointed me in the right direction, I realize I don’t even need to create the variable, I can just put my literal into the parameter and it works.
VAR maxdate VARCHAR2(20);
BEGIN
SELECT MAX(transdate) INTO :maxdate FROM TRANSACTIONS;
END;
select * from TRANSACTIONS where TRANSDATE= TO_DATE(:maxdate,‘DD-MON-YYYY’);
This seems to work and can solve the problem. I was trying to use the Toad variables, but this works right in the SQL statement.
Again - thanks for your help!! I was working quite a while trying to get that syntax correct.
Edited to create multiple output files, one for each row of the driving query.
Subqueries are always best if you can get your data that way, but you can use the Query Iterator action to turn database values into variables that Toad can use. Query Iterator is on the "Control" tab of the automation designer.
Then I dropped an "Execute Script" action on top of (not below) the Query Iterator action (notice how the tree line comes off query iterator).
then when I add variables for the script I can reference the query iterator variable. (Note: I had to change the output from "clipboard" to "Discard output" option to avoid a clipboard error. That's fine since we have a "spool" command in the script anyway)
When you run the action, the script will get executed one time for each row in the "Query Iterator" query, with the variable values corresponding to those rows.
Run as before...
and the output is too big to show in a screen shot but you get the idea.
How about this - In automation designer, is it possible to set a variable based on the result of a query and then use that variable to determine which steps to run. For instance, you might run a query to get a simple result like a date and then you want to run Action Step - lowdate if it’s < 1/1/2017, but run Action Step - highdate if it’s > 12/31/2016.
I see a test variable step, and I could use that to put execute steps in either the then or else sections, so that would work. However, how do I get the database query result into the toad variable for use in the test variable step?
But it looks like "Test Variable" won't let you choose a Query Iterator variable. I can fix that for the next version but it won't help you with 12.11.
It's not exactly the use of a variable, but the example below runs script1 or script2 depending on the query result...
