New to Toad, trying to use input parameters captured as internal variables for a generic automation workflow.
The scenario is simple: assume I have 100 reports that do the following: connect to Oracle, run SQL query, save results to Excel, zip the Excel file, email the zip file to stakeholders. So I created a generic workflow to encompass the above activities, inclusive of a scheduler component. The workflow needs to receive input parameters such as SQL file name to run, Excel report name, ZIP file name, email distribution list and so on in order to be able to run all 100 reports and future ones, as needed. I created variables for all the input parameters and for now I hardcoded them just to make sure that I can use them throughout the workflow (then I'll need to somehow set them from input parameters).
I have problems using a variable to specify the SQL file (where the SQL statements are) to be executed in the "Select to File" activity. It's very unstable but I was able to specify the output Excel report name as a variable (#Excel_Report_Name#). Not being able to specify the core SQL file to be executed for each report is a show-stopper because I would need to create 100 identical workflows just to be able to hardcode the specific SQL file to be run by each report which really defeats the entire purpose of automation.
Since I'm now to Toad I hope I'm missing a very basic feature which should allow me to parameterize my workflow by using input parameters bound to internal variables to be used throughout the workflow. I defined all variables in the "Set Variable" activity at the beginning of the workflow which should allow for visibility down the workflow activities.
Any ideas would be very much appreciated!