Hello,
I just started using Script manager in version 11 but will try v13. My question is that I have to format the query results in an excel delimited format.
Does anyone have any experience with formatting with script manager?
Thank you
Johnny
If you want to send script manager output to excel, then right click on one of the grids in the “Output” tab and choose “Export Dataset”
But you don’t have to use script manager to do that. It would be faster to either run your query in the Editor with F9, then right-click in the grid and choose “Export Dataset”, or just set up your export from Automation Designer, or if you are exporting a whole table, then right-click the table from the Schema Browser and choose “Export Dataset”.
-John
Hey John,
Thanks for your reply… I just wanted to automate everything with the running of scripts and results sent directly to a file ( excel in this case which is viewed better )
When I choose an xls format for the file on script manager output options , the output does not fall into the cells and not nice
Any ideas ?
Johnny
You can get Excel files out of Script Manager, but not automatically. It really wasn’t made for that. Try this instead.
-
Go to Automation Designer in Toad
-
Create an “Export DataSet” action (click “Export Dataset” on the toolbar to select it, then click in the area below to create it). Double-click it to set up query, export format, etc. Click “Apply” and close it when you are done.
-
Repeat the last step to create as many “Export Dataset” actions as you need.
-
You can select them one at a time to run, or click on the ‘App’ on the left to run them all.
Will try those steps and report back 
thx
I am not sure if i’m configuring the setup correctly but getting an ORA-00900 invalid sql statement error when running an SQL statement in the dataset properties screen,dataset, export query and exporting to excel
i.e just a simple query and it works fine in toad directly. Does it need to be a specific format ?
column SESSION_KEY format a10
column INPUT_TYPE format a10
column status format a10
column START_TIME format a15
column END_TIME format a15
select SESSION_KEY, INPUT_TYPE, STATUS, to_char(START_TIME,‘mm/dd/yy hh24:mi’) start_time, to_char(END_TIME,‘mm/dd/yy hh24:mi’) end_time, elapsed_seconds/3600 hrs from V$RMAN_BACKUP_JOB_DETAILS
where input_type = ‘DB FULL’ and STATUS = ‘COMPLETED’ --and STATUS <> ‘COMPLETED’
and start_time > sysdate - 1
order by start_time desc;
Take out the “COLUMN” lines. Your query starts with the word “SELECT”. Those lines that start with “COLUMN” are not part of your query, they are SQLPlus commands. Most of the time in Toad, you don’t need or want them. The only time they are helpful is when running as script (F5 in the Editor), or in SQLPlus.
I am getting the same error after removing those lines 
select SESSION_KEY, INPUT_TYPE, STATUS, to_char(START_TIME,‘mm/dd/yy hh24:mi’) start_time, to_char(END_TIME,‘mm/dd/yy hh24:mi’) end_time, elapsed_seconds/3600 hrs from V$RMAN_BACKUP_JOB_DETAILS
where input_type = ‘DB FULL’ and STATUS = ‘COMPLETED’ --and STATUS <> ‘COMPLETED’
and start_time > sysdate - 1
order by start_time desc;
I just pasted your query into an Export Dataset action and it worked n Toad 13. If you are still on Toad 11, you may need to remove the semicolon at the end.
Semicolons are also not really part of the query. SQL*Plus just uses them to mark the end of the query. They are helpful in the Editor where you might have multiple queries, but we always have to take the semicolon off in the background when we send the query to Oracle. That might not have happened in Export Dataset in Toad 11.
If you are on Toad 13 and getting the error, please post a screen shot (or email it to me: john.dorlon@quest.com).
I’m using toad 11 and will soon use 13. OK thanks it works now
My goal is to put several queries with some comments in the export dataset , is this possible in the same editor ?
So basically create a report from one or several databases using several queries and export them in a readable excel format
Export Dataset can only take one query at a time.
To get around that, make multiple Export Dataset actions - one for each query. If you want them to all have the same properties (except the query), a fast way to do that is copy/paste the action in the automation desgner.
You can then run them all by right-click on the App (on the left side of the Automation Designer) and choosing “run”.
I don’t think this was in Toad 11, but in Toad 13, there is an option to add a new sheet in the Excel file instead of overwrite.
OK will wait till 13 is installed and see what can be done as I really want to produce a periodic report within one worksheet.
your help is greatly appreciated !