0

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
Jim Johnson
  • 59
  • 1
  • 6
  • Step #5. Without seeing your SQL it's hard to say, but if you run your script in Toad's Editor do you get the same error? If so, where is the error marked? Does your script in fact have an invalid character? I'm unable to reproduce in Toad 10.6.1. – Michael S. Jun 10 '15 at 15:02
  • BTW, you had the dev team helping you when you asked here... http://www.toadworld.com/products/toad-for-oracle/f/10/t/24686.aspx You can be helped here too, but only a few members of the team monitor StackOverflow. – Michael S. Jun 10 '15 at 15:15
  • Michael S. Hi the script itself works when I run it outside the "Automation Design" portal but doesnt work using it. I did ask on Toad World but felt the "activity" was limited. Thank you. – Jim Johnson Jun 10 '15 at 16:11
  • Enable Spooling by toggling the "Spool SQL to Screen" option from the Database|Spool SQL menu. Run your script again and look for the error in the spooled SQL. See my answer though because if you're needing Excel file output then you can't do this with execute script. – Michael S. Jun 10 '15 at 16:17
  • Limited activity... :-) Your first post came after work hours and you had a reply @ 7:23 the next morning. Your next post had a response in 34 mins. It does take a few mins to read and respond sometimes....... – Michael S. Jun 10 '15 at 16:18
  • Yes, you're correct :). Sorry about that. I am trying out your instructions below. Just waiting for SMTP access. Thank you for your prompt replies. – Jim Johnson Jun 10 '15 at 18:44

1 Answers1

0

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.

Michael S.
  • 1,771
  • 15
  • 20