Automation Designer: Export Dataset new feaure

Toad for Oracle - Beta
1

Hello,

I just wanted to make a quick post about something coming in Monday's beta. Something that few people will find unless it's documented somewhere.

When using SQL Files as the query source, you can right-click on the filename box and choose "Variables". Until now, variables here were meant as part of the file name, like c:\temp\%my_awesome_variable%\test.sql. That works.

What does NOT work, until now, is using this variable as a means to pass a parameter into the SQL file. Like so:

image

In which case, the corresponding SQL File would reference the first parameter as &1, the second as &2, and so on. For example:

image

I hope someone finds this helpful.

-John

5 Likes
Toad for Oracle & Toad for Oracle on Mac 17.0.341.1977 is now available
2

Noted. This is brilliant!
Thanks!

3

Thanks @JohnDorlon for the feature.

It works well for one parameter, however I’d be happy to have some guidance for using two variables

I’m using this setup:

image
image872×667 9.69 KB

The script debug.sql has the following content:

SELECT '&1', '&2' FROM DUAL

According to the SQL trace it gets executed as follow:

SELECT 'a b', '&2' FROM DUAL

Trying out a few other conventions like puting the substitution variable in double quotes also lead to results different than what I expect. I’m expecting this query to be executed

SELECT 'a', 'b' FROM DUAL

4

Which Toad version do you have?

5

I’m using version 2024R2

More details:

TOAD FOR ORACLE BASE EDITION ENTERPRISE TERM LICENSE/MAINT Base Edition (64-bit)
24.2.275.4664

6

I just tried it and for me it worked as expected.

I think your %TRAD.VALUEA% is set to A and your %TRAD.VALUEB% is blank.

This is what I did (using built in variables)

image
image457×697 11.6 KB

image
image454×692 19.7 KB

my file looks like this:

select '&1' as first_col, '&2' as second_col
from dual;

and after I ran it I had this in the clipboard
FIRST_COL,SECOND_COL
9/23/2025 AZURE_12CR2_PLUG,&2

7

Thanks for the analysis.

In your example I see the following output:

FIRST_COL,SECOND_COL
9/23/2025 AZURE_12CR2_PLUG,&2

I understand this means that the substitution variable &1 contains both parameters and &2 is empty.

I was however expecting to see that output instead:

FIRST_COL,SECOND_COL
9/23/2025, AZURE_12CR2_PLUG

In other words, I’m wondering how to provide the parameters so that the substitution variables are allocated as follow:

  • 1 = SYSDATE
  • 2 = ACTIVESESSIONDB

Do you know what adjustment is required to achieve that output where both substitution variables are set?

8

Oops, you're right! I didn't even notice the &2 in what I pasted. Sorry about that.

It seems there is some bug here. I'll have to take a look at the code and get back to you.

1 Like
9

This will be fixed in the next beta.

1 Like