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!
While you can use automation variables to vary dynamically your output files from an automation flow, you can't use variables to specify an input SQL Script file, unfortunately. I would consider this an enhancement suggestion, if you could please visit our Idea Pond to submit it.
As a work-around, that hopefully meets your needs, you may want to consider using a driving file or table that includes all your inputted information, including any SQL Script logic that is necessary for each report to be produced.
I created a quick automation job that does pretty close to the requirements you outline, except I'm not accounting for bind variable input at the time of execution.... Theoretically, you could ask for input (bind) variables when the automation job starts. And, OK, I didn't include the Zip file step before attaching to e-mail, etc.
Screencap 1 below shows my example automation flow. I created a quick table called "LoopManifest" which contains a few of the info items you mention, including the query logic I want to execute, name of the output file, e-mail addresses, etc. This is my driving table for looping. I'm hoping that instead of pointing to SQL Script files, you could embed your logic as a column in your driving table.
Screencap 2 shows how I can set some of my variables (which I defined in the very first step of the flow beforehand) with the info I'm picking up row by row from my driving table.
This last screencap shows that I can dynamically specify
- The query I want to execute, and
- The output file name.
Hoping that this might give you a few ideas that get you closer to your end goal.
Gary - thank you for your thoughtful response.
We currently have exactly the setup you propose: a .NET program which uses a small MS Access table to get the inputs necessary for each report: SQL, email distribution, type of report output (Excel, PDF, etc), zip (yes, no), email (yes, not), etc. I wanted to move away from our home-grown program and use Toad for automation and, specifically be able to externalize the SQL in files which could be checked into a version control program.
Needless to say, I'm quite surprised that a basic feature like dynamically specifying a SQL file to run as input is not supported by Toad automation. I guess I'll have to learn some more Toad constructs (like the ones you mention above) to set my local variables to an input stream coming from a file or database.
Please let me know if you have any other ideas and again, thanks a lot!
Many Toad users have helped me out in these past years. Paying forward to others, where I can!
Please submit your enhancement to the idea pond, as I think it's a worthy suggestion.
Hi,
You are right that currently, we do not support variable path in Select to File activity. But we support it for example in Copy activity. I know this is not the best solution but maybe it should work for you if you want to work with dynamic SQLs.
Here is my example:
As you can see before each run I am able to copy my SQL file with variable to specific location and overwrite it. Then you can use this file in Select to File activity every run. I am using While activity and increment index but you can use any other way how to define the SQL path with variable.
Filip
Filip, thank you for taking the time to provide an alternate solution. This may be a bit convoluted as I don't want my script to write files to a directory where write-access may be denied. Plus the extra I/O with writing files may be a source of problems. I also want, if possible, to externalize the SQL queries in individual files for each report so they can be source-controlled (check in/out, etc).
My scenario is rather simple (I think): capture the common activities of my reports (connect, run SQL, save to Excel, zip if necessary and email to stakeholders) as a framework in one-only generic workflow and then use input parameters which are saved to local workflow variables to run specific reports (and use the scheduler for automation).
So, the scheduler wakes up my generic workflow (tas file) at the scheduled time and invokes it with an input parameter which could be the report id to be run. The workflow uses the report id input parameter to look up the details of the report in a file (database, maybe). In that file, I would have the report id and its corresponding data such as: the SQL file name to run, the Excel/Zip file name to produce and the email distribution list to name a few (I could have flags Y/N for email or zip and account for that with "if" logic in the workflow in case email should not be sent out for whatever reason or zip shouldn't be done if the number of records in the report has not reached a certain threshold). The idea is not to have to clone and copy the workflow for all reports (I have hundreds of them now) just because the workflow is not smart enough to use input parameters bound to variables which would suffice to invoke any current or future report.
Can I have the scheduler pass input parameters to my workflow (tas file) to be bound to local workflow variables and if so, how would I do that (is there an "Input Parameters" activity, etc)? My background is in software development and architecture so maybe I'm trying to do too much programming which may not be suitable for the Toad world (although, what I'm asking seems to be rather basic). Given the current shortcoming(s), I really can't recommend Toad to my team so I'm hoping that my lack of experience with Toad is the problem and not the Toad functionality itself (does Toad have a programming/scripting language that one can use aside from the visual drag-and-drop aspect?).
Again, any help would be appreciated!
