Toad for Oracle Base Edition (64-bit)
Add-Ons:
25.1.20.5192
In Toad Automation Designer, Export Dataset doesn't seem to be running SQL Text as the current connection when running in a connection iterator. This is quite a serious bug, especially if personal identifiable data involved (GDPR) or causing data corruption if the export is imported elsewhere.
Dummy example:
Connection Iteration (several connections)
Query Iterator (called QI1 - to get some variables, schema as USR and YYYYMM date as DT)
Export Dataset (select USER from dual, using filename %QI1.USR%_%QI1.DT%.txt)
The files are created correctly, but the content in each file is showing the first connection's user. In reality Export Dataset exports more complex non schema qualified queries as our customers have their own schemas, but have the same data model.
I am able to reproduce the problem. Thank you for reporting it. I will have it fixed in Monday's beta.
The problem (in our code, not your process or assumptions) is that the connection iterator is only assigning the connections to actions immediately under it, so grand-child actions and those below are excluded.
As a workaround, you can do this instead, assuming that your query iterator only returns one row:
Note, the Export Dataset is now a child of Connection Iterator, not Query Iterator.
Connection Iteration (several connections)
Query Iterator (called QI1 - to get some variables, schema as USR and YYYYMM date as DT)
Set Variable (set a variable called V1 to %QI1.USR%
Set Variable (set a variable called V2 to %QI1.DT%
Export Dataset (select USER from dual, using filename %V1%_%V2%.txt)
This is how it looks in Toad (my variable names were slightly different)
Mmm, I am trying to run multiple scripts based on the query iterator.
for a list of connections
for a list of dates from a query
Export Dataset 1 (based on current date)
Export Dataset 2 (based on current date)
....
Export Dataset n (based on current date)
I don't suppose you can think of a workaround for that without a long winded copy paste?
or...and I didn't test it but I think it will work...
If your query iterator is just a list of dates and doesn't depend on any particular connection, you could do this:
for a list of dates from a query
for a list of connections
Export Dataset 1 (based on current date)
Export Dataset 2 (based on current date)
....
Export Dataset n (based on current date)
As part of this automation, I need to pass a date returned by the query iterator to a script or sql text as part of "Execute Script". How do I do that, my attempts are failing?
e.g.
begin
MyProc(:DATE_PARAM); -- << Date needs to go here
end;
Oh, so you have a date already out of query iterator. The problem is figuring out how to use it in a script?
I have a dropbox folder with some documents I've written about Toad and Oracle stuff. A few of them are about automation designer. Look at the one called "Automation Designer - Passing multiple parameters to a script". This shows how to pass variables to a script (but nothing specific to dates)
It might work best returning your date as a string with to_char in some known string/numeric format like 202500414, then your script could convert that back to a date if needed. I don't think parameter values can be passed in date form to a script