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
If it’s the only thing in the file you can do it like this:
-
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)
-
drop a file iterator action in the automation designer. Point it to d:\file.txt
-
drop an export dataset action on your file iterator action, so the export dataset action is nested under it, not just “after” it.
-
in export dataset action, go to the “dataset” tab and make your query. Mine is:
select *
from dba_Tables
where owner = :own
-
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.
-
run the “File Iterator” action, and it will in turn run the export dataset with the value in your file.
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 !
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.
I am using 12.9
It was added in version 12.11.
Thank you !
As a workaround, maybe you could put your variable value in the database.
Then join your query to whatever table has the value.
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.
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.
So my next doubt is if i go for upgrade then my current license will hold out for the updated version?
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.
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…
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?
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.
Can you please help me with an example.
Also can I do an insert operation using the same query iteration?
Sorry for bugging up again.
Automation is working good with use of variables from input file.
Can i apply some changed so that query will use the variable from input file 'one-by-one' as it contains date. so i wanted to fed all the dates into input file and schedule it so that it will pick date as variables from file one by one or next line in each run.
Yes, that's what the iterators were really designed for.
Put multiple lines of data in the file.
Run the iterator
The actions below it run multiple times, one for each value in the file.
or are you wanting to run it daily, with each day's run use the next line in the file?
I am using two file iterator as child of first with dates 'line by line' and then exporting the dataset.
So you mean to say if i schedule it to run then it will automatically iterate to next line of both the files. Do i have to make any changes in iterators or add new one on outer loop?
If I understand you correctly, you only need one file iterator.
I have a word doc describing how to do this. Thought I could upload it here but I guess not. I put it in my dropbox. https://www.dropbox.com/sh/122c8wmmfrd2vgn/AAAsO1aKKSyIyOKlpGGEyxQHa?dl=0