Automation Designer - Import and run from .sql file Export text file

Toad for Oracle
1

I have a sql file (query.sql) that is generated by a script and needs to run and have the results exported to a flat file daily. The issue is that query.sql changes every day so I can’t use the Export Query option in the Export Dataset module. I can run the query.sql in the main window by using:

@ C:\Users\Desktop\query.sql

However, I want to be able to set it up in automation designer. Ideally I would want to combine the two and do something like the image below where I call a query into the export dataset then export the results, but that causes an invalid SQL statement error. Is this possible or is there another way to approach it?

Thank you for any suggestions. I am running Toad for Oracle 12.11.0.95 64 bit.

ScreenCapture.png

2

Try this:

  1. temporarily at least, put whatever your current SQL is in the Export Query tab.

  2. Set up the action and click “Apply”, then close it.

  3. right-click on the action and choose “Create parameter file”. This file contains the query.

  4. have your nightly process create a file like that parameter file, except with whatever the current query is.

  5. You can then right-click the action and choose “run with parameter file”, or to run it from command line,. do something like this: "C:\path…to…toad\Toad.exe -a “App->Export Dataset1 | c:\ExportDataset1.ini”

3

I like the idea of being able to put @filename in there.

4

Another workaround could be to have your file generation process write the records in CSV and run the file using Execute Script action, instead. Also have the generation process include the header row, if desired, by selecting from DUAL. Example file contents…


SET FEEDBACK OFF
SET PAGESIZE 0
SPOOL c:\testing.csv
SELECT 'TABLE_NAME,TABLESPACE_NAME' FROM DUAL;
SELECT table_name || ',' || tablespace_name
FROM all_tables
WHERE owner = 'SYS' AND tablespace_name IS NOT NULL;
SPOOL OFF

If you don’t want to include the output file in the script then omit the SPOOL commands and set the Execute Script output option to “Use single file” and specify your filename there.

Michael

5

That works great! Thank you. I didn’t think to generate a separate parameter file.

I like the spool option too, but the size of the dataset sometimes becomes too large and the extract takes much longer.

6

This has come up twice recently, so in the next beta you will be able to specify a file name instead of SQL text in Export Dataset.