Hi there,

I have a query that I would like to automate: not only the query itself but also the results. I think this can be done by creating a spool file?

Yes, query results can be spooled into a file.

But, until you tell us which DB you use, it is hard to tell the correct answer.

Ok, I use Toad en SQL*PLus on Oracle databases.

What I would like, is the following:

I have already designed a query, that produces (on screen, not in a table) the number of customers per month.

I would like to automatically run this query without manually starting Toad and/or SQL*Plus and to save the results in a txt file on my c drive.

To run the script automatically, use some kind of a scheduler on your computer (MS Windows, for example, offer "Scheduled tasks" under Accessories - System tools). I'm not sure can it be done through TOAD (but I'd bet NO if you ask me), but I'm sure it CAN be done in SQL*Plus.

Spooling in Oracle can be done using SPOOL command in SQL*Plus. A very simple SQL script example would be this:

spool output.txt
select * from emp;
spool off;

Ok, so I can give my script - including the spool command - a name and save it as e.g. c:/test.sql.

How do I than schedule it via MS Windows? The wizard talks about the application to select (here,SQL Plus), but how does Windows know that it only shoudl run my c:test.sql??

To run a .sql script, you will need another - batch (.bat) script. It should look like this:

SQLPLUS username/password @test.sql

This is SQL*Plus User's Guide and Reference; you might want to read it, especially Chapter 5: Using Scripts in SQL*Plus

You have to help me here...

This is my query:
SET TERMOUT OFF;
SET ECHO OFF;
SPOOL c:/test.txt
select to_char(a.day_of_creation,'yyyy-mm') as month,count(distinct a.customer_accountno) as aantal
from ecl_cases a
where a.CASE_TYPE<>'BLOKSTORINGEN'
and (to_char(a.DAY_OF_CREATION,'yyyy-mm-dd')>='2004-10-01' and to_char(a.DAY_OF_CREATION,'yyyy-mm-dd')<='2004-12-31')
group by to_char(a.day_of_creation,'yyyy-mm')
order by to_char(a.day_of_creation,'yyyy-mm') asc;
SPOOL OFF;

I have saved this as test.sql

So, now I would like to only double-click on this file in Windows explorer to run it. So, without starting SQL*Plus myself. What should I do than?

I tried your bat file option, but the MS-DOS screen gives:

ORA-12560: TNS: protocol adapter error

Do I have to put the database-name (server) aslo in the bat.file?

For example, those two files might look like below. You would double click on P.BAT file to run P.SQL file and have output stored in P.TXT file.

REM P.SQL FILE
spool p.txt
select count(*) from tab;
spool off;
exit;

REM P.BAT FILE
sqplus -s username/password@database @p.sql

Yep, it works! Great, thanx for all your help!

No problem, I'm glad you've made it.