Automation Designer: use query iterator to set a variable with current year

Toad for Oracle
1

Hello!! i am trying to ser a variable with to_char(sysdate, 'YYYY') but I am not able to do that :frowning:

the last think I have tried is:

  1. create a query iterator qi1 with select to_char(sysdate, 'YYYY') query_year from dual
  2. inside iterator set a variable with value %qi1.query_year%

example

but it does not work.

Please, do you know how I can assign to_char(sysdate, 'YYYY') to a variable ?

Thanks in advance!
Jordi

2

The Query iterator makes the variable and sets it. You don't need to set it manually after that.

Keep your query iterator action. Get rid of the Set Variable action. Just use %q1.query_year% as your variable in some other action.

3

hi John, many thaks. Just to understand. If I insert a message:

521

the variable lose its value. Why it does not maintain it?

thanks a lot.
Jordi

4

Message2 is not part of the iterator, it's just after it.

See how "Set Variable1" is indented under qi1? That's where you want Message2 to be. Then it will keep the value. You can drag the Message2 action onto qi to make it a child action.

Also, just FYI - if your query in the query iterator were to return multiple rows, then the actions indented under it would run once for each result from the query iterator.

I have a dropbox folder where I've written some documents about Toad. https://www.dropbox.com/sh/122c8wmmfrd2vgn/AAAsO1aKKSyIyOKlpGGEyxQHa?dl=0

There are a couple in there about automation designer. See the one called "Automation Designer - Choose which script to run based on a variable.docx" It has an example of Query Iterator.

5

Thanks john, I review your documents.

However, I created the variable before the iterator, and I supposed that with the iterator I could set it with current year in order to use it later.

522

Regards!
Jordi

6

Oh, I understand. It seems like that should work. I'll check

7

We don't support setting of one variable from another.

I'll log it as a bug and will have it fixed soon.

In the meantime, just use the qi1.query_year variable in whatever action you need it. You can drag multiple actions under the query iterator if needed.