Bind variable while using folder iterator to export Excel Files from multiple queries

[RaphG]

Hello,

I'm trying to automate the execution of a set of queries in a folder.
They all have a date as a parameter.

If I create/set the variable at the begining, and use the dataset export /SQL Text, I can easilly set/bind the variable that I had previously entered.

However, if I have the exact same query in a folder and I use the Folder Iterator to do the same exports, I cannot set/bind my variable for all the queries to run and thus the date I had set is not taken into account.

Is there a way around this ?

I'm using Toad for Oracle 13.3.0.181

Thank you

[RaphG]

[RaphG]

[RaphG]

[JohnDorlon]

So, to make sure I understand, you want to:

For each SQL file (all having the same variable) in a folder,
Load them into Toad,
Set the variable
Export the result (presumably to separate files)

The tricky part is declaring and setting the variable. I don't know of a way to do that - to stick something in between the Folder Iterator and Export Dataset which will handle the variable.

I think you'd have better luck copying the folder to a new location, then running some process to substitute in the variables with literal values in the files, then load those files into Toad and run them.

I have this dropdbox folder with some docs on Automation Designer, but nothing quite like what you're after. I hope it is helpful in some way.

Toad

[RaphG]

Thank you for your quick reply.
Indeed, I have dozens of report queries that I want to run and export the results in xlsx.
At the begining of each of them, I set a date parameter.

What I wanted to do is, with toad, scan the directory and run+export all the queries with a parameter that I would have set (the date).

You folder is very interesting, thank you for sharing.

I will explore other options, but it would have been great to have that in Toad

[JohnDorlon]

Here's another possibility:

Set your date value in the database somewhere (a table with one row, one column like DUAL except with your date value).

Modify your queries to get their date value from that table.

Before your Folder Iterator action, place a script action to update the value in that table.

[RaphG]

Thanks again for the quick follow-up.
Unfortunately, I don't have write access (and my version of Oracle is too old to have the personal temp tables), so I ended up using XCopy and Powershell to modify all the dates in the queries.
Next step: looping with an arry of dates ...