Spooling in SQL

[RW10]

Hi,
I'm trying to spool to a text file, but if the file already exists, instead of replacing the contents, text from the spool is added to the end of the file. Is there a way to have the spool completely replace the contents of the file?
Thanks,
RW

 

[SantaMufasa]

RW,

Something is squirrelly in your case. If you issue these commands:

SPOOL <file>
SELECT...
SPOOL OFF
SPOOL <same file>
SELECT...
SPOOL OFF

...the only contents of the file will be the most recent output since the most recent SPOOL command. Here is proof of that assertion:

SQL> select to_char(sysdate,'yyyy-mm-dd hh24:mi:ss')now from dual;

NOW
-------------------
2006-09-15 09:49:14

SQL> spool off
SQL> get x.txt
  1  SQL> select to_char(sysdate,'yyyy-mm-dd hh24:mi:ss')now from dual;
  2  NOW
  3  -------------------
  4  2006-09-15 09:49:14
  5* SQL> spool off
  6  .
SQL> spool x.txt
SQL> select to_char(sysdate,'yyyy-mm-dd hh24:mi:ss')now from dual;

NOW
-------------------
2006-09-15 09:49:32

SQL> spool off
SQL> get x.txt
  1  SQL> select to_char(sysdate,'yyyy-mm-dd hh24:mi:ss')now from dual;
  2  NOW
  3  -------------------
  4  2006-09-15 09:49:32
  5* SQL> spool off

I'll bet that you are not doing a "SPOOL OFF" command between the spool commands.

Let us know.


Mufasa
(aka Dave of Sandy, Utah, USA)
[I can provide you with low-cost, remote Database Administration services: see our website and contact me via www.dasages.com]