SQL Spool FILE SQLPLUS BATCH FILE

Question

*/
SET colsep ';'
SET TERMOUT OFF
SET NEWPAGE 0
SET SPACE 0
SET LINESIZE 800
SET PAGESIZE 0
SET ECHO OFF
SET FEEDBACK OFF
SET HEADING OFF
SET NUMW 20
column timecol new_value timestamp
column spool_extension new_value suffix
SELECT '.csv' spool_extension
FROM dual;
/
SPOOL \\SERVERNAME\FOLDER\FOLDER\FOLDER...ETC\InternationalReport&&suffix
@@"C:\FOLDER\FOLDER\International_report\International";
/
Exit;

Currently, it will spool the .csv file but I need it to have an output of InternationalReportyyyymmdd.csv

Any ideas on how exactly to set this up? Thanks!

• miracle173: to set suffix to '.csv' it is simpler to use DEFINE SUFFIX=.csv
• Hector: Nice, thank you for that tip miracle173, I will use that next time!

Answer 1

column timecol new_value timestamp
column spool_extension new_value suffix
SELECT TO_CHAR(SYSDATE,'YYYYMMDDHH24MISS')||'.csv' spool_extension
FROM dual;

SELECT TO_CHAR(SYSDATE,'YYYYMMDDHH24MISS')||'.csv' spool_extension -- Solved the problem after I added TO_CHAR(SYSDATE,'YYYYMMDDHH24MISS')

Hope this can help someone else who is looking for a solution