How to export query results directly to a file (4312471)

Is it possible to export query results directly to a file, without having to have the results returned to a data grid?

RESOLUTION 1:
(for Toad 9.6 and up)

You can use the Automoation Designer (formerly konwn as Action Recall and AppDesigner) to export query results to a file.

1. Select Utilities | Automation Designer.
2. In the Import/Export tab, double click on the Export Dataset icon in that tabs toolbar. This will add an action in the window below.
3. Double click on the Export Dataset action that was created.
4. In the window that pops up, click on the Dataset tab. Make sure “Export Query” is selected and enter the query you want the data for.
5. Select the Options tab and fill in the necessary information.
6. Click Run and the file will be created.
7. If you want to run this again later, select Utilities | Automation Designer, right click on the task, and select to Run.

(for Toad 9.5)

You can use the action palette to export query results to a file.

1. Go to View | Action Palette.
2. On the Exporting tab, click the icon labeled Export Dataset. You will be prompted to enter a name for his action, and then it will be created in your list of actions under the vault tab (as shown below as "Export test_spool").
3. Double click on the action created and it will open up the properties window.
4. Specify the Export format and other settings, and file name and path on the Options tab, then specify the query on the Dataset tab.
5. Click Apply to save the information. Or click Run to run the action at that time. If you click Apply, the properties window will close and you can schedule the export action to run later or run the export action then. The options are at the top of the Action Pallet. The green arrow is the Execute Selected Action button you can use to run at that moment. The square icon just below it is the scheduler option and will help create and schedule a job.


RESOLUTION 2:
(for any version of Toad)

You can apply the SQL command of SPOOL to your query/script and direct the information to be saved onto an excel file. You would need to run this in Toad editor with F5 run as script.

By default Oracle can take a path or you can just note a file name (you'll need to check with Oracle about the default directory if you just set a file name). By default Oracle will create a spool file of extension LST, but it looks like it will uphold basic file extensions like TXT or XLS, so you can specify an extension with the file name. But Toad cannot help you with any formatting issue in this case since it is an Oracle command and Toad it just running it as Oracle would.

Example:
-- No path specified in these cases. If done from Toad's editor, it will automatically be created in Toad's User Files folder in the installation directory. -- Check for Oracle's default directory in this case if not working from within Toad.

spool test_spool; -- will create toad_spool.LST
select * from bonus;
spool off;

spool test_spool.TXT; -- will create toad_spool.TXT
select * from bonus;
spool off;

-- You can specify the path of where you want the spool file to be created.

spool c:\test_spool.XLS; -- will create toad_spool.XLS
select * from bonus;
spool off;

In Toad, if you do not specify a path, and only note a file name as shown below, it will by default create the file in your Toad install directory in the User Files folder (C:\Program Files\Quest Software\Toad for Oracle\User Files).

Here is the Toad Help info about the "SPOOL" SQL Command:
SPOOL Filename and SPOOL OFF (also SPO).

· In Toad, non-pathed files are created in the Toad directory.
· In Quest ScriptRunner, non-pathed files are created in the same directory as Quest ScriptRunner.
· Supports relative spool paths
· Like SQL*Plus, passes SPOOL file to sub-scripts

To save data that has already been run, you can use the Save As option to save to a file. Please refer to Sol35413 for further information.