-
Title
How to automate and schedule execution of a query or a SQL script file via Toad? -
Description
How to automate and schedule execution of a query or a SQL script file via Toad?
-
Resolution
Toad for Oracle 9.7 and below:
1. Save query into an SQL file, if needed.
2. Select View | Action Palette menu option.
3. Select "Misc" tab located next to "Exporting" tab.
4. Select the icon with a yellow lighting bolt, called Execute Script.
5. Give the Action a name and hit OK.
6. Double click on this action, listed in the "Vault" tab.
7. Give action a description, if desired, and hit the Add button.
8. Locate the SQL file. Note, multiple SQL files can be added with the Add button.
9. Specify the location for query results under Output section.
10. Hit Run, select database connection information and hit OK.
11. Back in Action Palette, right-click on action and select Schedule.
12. Hit Next on the first screen, name the task and identify it's time to run.
13. In the last screen of the wizard, you will have to enter Windows login name and password.
14. Hit Finish.For Toad for Oracle 10.0 and above:
1. Select Utilities | Automation Designer menu option
2. Select DB Misc tab from right side pane
3. Last icon in this tab is Execute Script, double click on it to add under Action list
4. Double click on Execute Script 1 action to open it
5. Select Text radio button and enter query statement or File radio button and point to SQL file
6. Under Output pane, designate the Output destination, perhaps a file and if so, provide path and file for output file
7. Hit Apply and Cancel
8. Right click on Execute Script 1 action and hit Run. Check the above output path and file to ensure its created
9. Right click on Execute Script 1 action and hit Schedule to schedule this execution. Note this is handled by Windows Scheduler and you will need to enter Windows login credential at the last pane of wizard
10. Select Utilities | Task Scheduler menu option to view this job and its status