Toad World® Forums

How to set a variable from a file in the Automation designer


#1

I would like to run my sql query through the automation designer. Instead of opening daily my export task where my sql query is stores to update the variable date every time I run my queries I would like to read the input variable from a txt file. How can I do that ? Thanks


#2

If it’s the only thing in the file you can do it like this:

  1. create your file (in my example, I’ll call it d:\file.txt, and the only thing in the file is SCOTT, followed by a line feed)

  2. drop a file iterator action in the automation designer. Point it to d:\file.txt

  3. drop an export dataset action on your file iterator action, so the export dataset action is nested under it, not just “after” it.

  4. in export dataset action, go to the “dataset” tab and make your query. Mine is:

select *
from dba_Tables
where owner = :own

  1. then click the “Set Variables” button. Click the “Value” setting to “Environment variables” and choose “File Iterator1” (or whatever the name of your file iterator action is). Set everything else in the export dataset action, then click “Apply” and close it.

  2. run the “File Iterator” action, and it will in turn run the export dataset with the value in your file.


#3

Thanks John for your answer !

I am trying to do that but I do not see my file iterator action name in the list of “environment” variables and when I input its name here either with “name” or %name% it returns an error ‘ORA-00911: invalid character’

My variable is an integer 20180416

Does it change anything ?

Thanks !


#4

which version of Toad are you using? I think the export dataset action started supporting the file iterator variables fairly recently. I tested with 12.12.


#5

I am using 12.9


#6

It was added in version 12.11.


#7

:frowning:

Thank you !


#8

As a workaround, maybe you could put your variable value in the database.

Then join your query to whatever table has the value.


#9

Many thanks for the answer. This is what i was wanted to acheive. Every step you told i followed but the SET Variable option does not appear in the Export DataSet action.


#10

The “Set Variables” button was added for Toad version 12.8, so if you are using an older version, that is why you don’t see it.


#11

So my next doubt is if i go for upgrade then my current license will hold out for the updated version?


#12

I am not sure if this answers your question, but If you are able to upgrade, you can keep your old version installed and have the new version installed also.


#14

Hi I have installed the latest version which supports the set variable option. I have Created the file and have kept some mumbers as value and created the action of file iterator and export dataset.
But now it is giving the errror as ‘%File Iterator1%’ is not a valid integer value.Uploading… Uploading…


#15

I have a requirement where in I need to pass three parameters eg: select * from employee where emp_x= :x and emp_y =:y and emp_z= :z. So in this case which iteration need to be used. List or file?


#16

Neither of those will let you get multiple variables in a single SQL like that. A query iterator could do that, but if you could get your variable values by a query, then could could just to it in the SQL itself.


#17

Can you please help me with an example.

Also can I do an insert operation using the same query iteration?