Home » SQL & PL/SQL » SQL & PL/SQL » Spooling sql*plus file
Spooling sql*plus file [message #216578] Mon, 29 January 2007 08:45 Go to next message
ora_dev
Messages: 27
Registered: December 2006
Junior Member
Hi

I have a script written in sql*plus that creates a csv file. To create the file I use : spool c:\demo.csv

I now want to run this script every night but want to automatically pick up the system date. The output file would therefore be 20070129.csv instead of demo.csv.

How do I change the spool command to accomplish this.

Thanks
Re: Spooling sql*plus file [message #216579 is a reply to message #216578] Mon, 29 January 2007 09:11 Go to previous messageGo to next message
Mohammad Taj
Messages: 2412
Registered: September 2006
Location: Dubai, UAE
Senior Member

HI,
try below solution
SQL> column today new_val dt
SQL> select to_char(sysdate,'yyyymmdd') today from dual;

TODAY
--------
20070129

SQL> spool d:\&dt..csv
SQL> spool off
SQL> host dir d:\
 Volume in drive D has no label.
 Volume Serial Number is 54DF-1372

 Directory of d:\

01/29/07  07:09 PM                16 20070129.csv
SQL>



According your OS schedule.

regards
Taj
Re: Spooling sql*plus file [message #216699 is a reply to message #216579] Tue, 30 January 2007 01:11 Go to previous messageGo to next message
ora_dev
Messages: 27
Registered: December 2006
Junior Member
Many thanks Taj, u a star.

Works like a dream.

Re: Spooling sql*plus file [message #216700 is a reply to message #216699] Tue, 30 January 2007 01:15 Go to previous message
Mohammad Taj
Messages: 2412
Registered: September 2006
Location: Dubai, UAE
Senior Member

hi,
thank you for your complimant.
but please i am not "u a star".

i am also learning new thing everyday and above example is part of it.

regards
Taj

Previous Topic: UPDATE A ROWS IN A TRIGGER FOR SAME TABLES
Next Topic: Delete duplicate rows,shows odd number of rows
Goto Forum:
  


Current Time: Sat Dec 03 18:08:11 CST 2016

Total time taken to generate the page: 0.15631 seconds