Toad: 10.6: Seek clear instructions on automating reoccuring excel reports

Question

I am new to Toad 10.6.1 and have a question about running a SQL script and email data as an excel file on a scheduled basis.

How do I run script and save as excel format and email as excel file to automatically? Thank you for your time.

Below is what I've done so far but dont think its correct as I get "ORA-00911: invalid character" on step #5 .

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 (I keep getting "ORA-00911: invalid character" as well)
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. Under utlities I added the email function, but unable to integrate steps 6 & 9 to automate report

Answer 1

When you say "script" are you really trying to export the results of a single query to Excel and then email that? If so, ditch the execute script action. Use the "Export Dataset" action instead which is intended to run a query and export the results to various formats. Execute Script is intended to emulate SQL*Plus functionality and your output is limited to text file.

1. Drop an "Execute Dataset" action into your app. It's available from the Import/Export tab in Automation Designer.

2. Double click it to edit its properties.

3. Select "File" as the output destination and click the "..." button to the right of the filename. Set your filename and choose the .xls file format. Set any other options as you see fit.

4. Select the Dataset tab and select "Export query." Enter your SQL there.

5. Apply and Cancel.

6. Drop an "Email" action into your app following your "Export Dataset" action. It's available from the Utilities tab.

7. Double click it to edit its properties.

8. Configure all properties as required for your mail server. *** Note that 10.6 only supports simple mail servers and no SSL/TLS so you can't use your Gmail account or anything like that. It's limiting. Newer versions of Toad support Gmail and the like.

9. In the Attachments area click "Add File" and specify your exported .xls filename.

10. Apply and Cancel.

Now when you run your app it will export to XLS and email the file.