Toad Automation Designer - Export Dataset Bug

Toad for Oracle & DevOps Toolkit
1

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.

Paul

2

Hi Paul.

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)

image
image1079×750 34.6 KB

3

Thanks for the workaround, John.

1 Like
4

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?

5

Wait until Monday and run it with the beta?

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)
6

Unfortunately, the dates are connection dependent and I needed to get an extract done today. I'll check back for the beta Monday. Thanks anyway.

7

I don't have any other ideas, sorry. :frowning:

8

Hi John, thanks for the Beta change for this.

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;

Thanks
Paul

9

Can you make it a function instead? Then you can use "select my_func from dual" in the query iterator.

10

Unfortunately it contains DDL.

11

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

12

Hi John

I tried as suggested, but when the parameter is passed, it is only passing 2 chars of the total source string

E.g. A Date (as string) from the query iterator => DD/MM/YYYY => 09/01/2025

In the Execute file I have:

begin
  MyProc(to_date('&1', 'DD/MM/YYYY'));
end;

What gets passed to &1 is only '09'. Any ideas why?

13

I've sussed it. SQL*Plus splits command-line arguments on spaces or slashes, so I suspect Toad passes:

sqlplus user/password@db @myscript.sql '09/01/2025'

SQL*Plus sees three separate parameters:

09/01/2025 → becomes 09, /01/2025, etc.

I've removed '/'s

1 Like