Results 1 to 11 of 11

Thread: Spool file?

  1. #1
    Join Date
    Jan 2006
    Posts
    9

    Unanswered: Spool file?

    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?

  2. #2
    Join Date
    Jan 2004
    Location
    Croatia, Europe
    Posts
    4,094
    Provided Answers: 4
    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.

  3. #3
    Join Date
    Jan 2006
    Posts
    9
    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.

  4. #4
    Join Date
    Jan 2004
    Location
    Croatia, Europe
    Posts
    4,094
    Provided Answers: 4
    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;

  5. #5
    Join Date
    Jan 2006
    Posts
    9
    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??

  6. #6
    Join Date
    Jan 2004
    Location
    Croatia, Europe
    Posts
    4,094
    Provided Answers: 4
    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

  7. #7
    Join Date
    Jan 2006
    Posts
    9
    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?

  8. #8
    Join Date
    Jan 2006
    Posts
    9
    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?

  9. #9
    Join Date
    Jan 2004
    Location
    Croatia, Europe
    Posts
    4,094
    Provided Answers: 4
    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

  10. #10
    Join Date
    Jan 2006
    Posts
    9
    Yep, it works! Great, thanx for all your help!

  11. #11
    Join Date
    Jan 2004
    Location
    Croatia, Europe
    Posts
    4,094
    Provided Answers: 4
    No problem, I'm glad you've made it.

Posting Permissions

  • You may not post new threads
  • You may not post replies
  • You may not post attachments
  • You may not edit your posts
  •