Invoked Toad and executed query file from command line. Cannot automatically export result dataset

Question

I am trying to automate execution of few TOAD based queries from the Command line. I'm using:

Toad.exe -c schema/pwd@myOracleDB -f C:\temp\sample.sql /exec

On running this, TOAD opens up and the SELECT query in sample.sql is run. Please suggest some means by which I may specify on the command line itself that I want the output dataset saved as some "mypath\myoutput.xls".

TOAD help does explain exporting reports via command line, but that seems to involve setting up a formatted report or some apps at the Automation Designer within TOAD first. I'm trying to avoid that path as I know very little on this.

Thanks, Somdeb

Answer 1

I just took a look and the steps should generally be the same for all versions of Toad with the Automation Designer.

1. Open the Automation Designer from the Utilities menu.
2. By default there are a couple of sample apps and an "App1." You can create a new folder, new app, or rename App1 if you'd like, but once you have an app to drop your action in you want to select it and then activate the Import/Export tab.
3. Double click the new Export Dataset action to add it to your app.
4. Double click the action in your app (or rt-click and chose Properties...).
5. Associate the action with a connection using the normal Change Session button on the toolbar.
6. Activate the Dataset tab and check the Export Query radio button.
7. Enter your query.
8. Activate the Options tab.
9. Change the Export Format dropdown to Excel File.
10. Select the File radio button as the Output destination.
11. Enter the filename to hold your results.
12. Change any other options as you see fit and click Apply and then close out of the properties dialog.

So now you have an app that can be later executed by command line, from within Toad, or even scheduled using the Windows Task Scheduler. To execute the app from command line you would issue the following command. In this example my action was named "Export Dataset1" and it is located in the "App1" app.

>Toad.exe -a "App1->Export Dataset1"

You can also execute the app as a whole using this command.

>Toad.exe -a "App1"

You mentioned having a few queries to export. If you add multiple export dataset actions to one app then you can run the app as a whole and get all of the results, or create a new app for each query if you don't want to run all at the same time, or add them all to the same app and execute each action individually using the first command I've shown above. The organization is all up to you.