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
@JohnDorlon This issue still seems to exist on Toad for Oracle Base Edition (64-bit)
26.1.193.8012.
In my example, for a given connection, I have a script which sets state (say via a package procedure) and the Export Dataset is using a query reliant upon that state, however, it doesn't seem to be set. Is it spawning a new session?
I just tested your "Dummy example" from your original post above. Connection iterator makes each connection (unless it's already connected), then query iterator and export dataset use that connection (not making their own).
Ok, I'll bear that in mind in future. However, that seems a bug to me and quite an insidious one. I would've thought the whole point of things like a connection iterator, is that every action beneath that should use that connected session. Otherwise you risk transactional and state issues, don't you, which could be dangerous to results?... As I have found!
Kind regards
I think you have a valid point about the importance of running things in the same session when you are using package variables. It's not obvious that things are going to run in a separate session....But we do have that "Execute scripts in Toad session" option. Would some consider it a bug if script actions just ignored that?
Maybe the best solution here would be to add an option in "Execute Script" actions to run in their own session or use the main Toad connection, or follow the setting in options.
Or even just an indicator on the Execute Script action to let you know how the setting in the options window is set.
