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)
In the script, the first one is &1 like before.
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.
