Export SQL query output to a file in a scheduler job I need to export data to a csv file in a scheduler job. Here are the commands:

SPOOL "\\AServerName\Output\RMS_DataFileSizes.csv" REPLACE;
SELECT /*csv*/
  TO_CHAR(SYSDATE,'YYYY-MM-DD HH24:MI:SS') AS "CurrentDTM",
  df.tablespace_name AS "Tablespace Name",
  SUBSTR(df.file_name,INSTR(df.file_name,'RMS_'),20) AS "File Name",
  Round(df.bytes/1073741824,2) AS "Size (GB)",
  decode(e.used_bytes,NULL,0,Round(e.used_bytes/1073741824,2)) AS "Used (GB)",
  decode(f.free_bytes,NULL,0,Round(f.free_bytes/1073741824,2)) AS "Free (GB)",
  decode(e.used_bytes,NULL,0,Round((e.used_bytes/df.bytes)*100,1)) AS "% Used"
FROM DBA_DATA_FILES DF,
     (SELECT file_id,sum(bytes) used_bytes FROM dba_extents GROUP by file_id) E,
     (SELECT sum(bytes) free_bytes,file_id FROM dba_free_space GROUP BY file_id) f
WHERE e.file_id (+) = df.file_id
  AND df.file_id  = f.file_id (+)
  AND df.tablespace_name = 'RMS_DATA'
ORDER BY df.tablespace_name,df.file_name;
SPOOL OFF;


When I make the "type of job" a "PL/SQL Block", it fails and I get the following error:

"ORA-06550: line 1, column 764:
PLS-00103: Encountered the symbol "\\AServerName\Output\RMS_DataFileSizes.csv" when expecting one of the following:

   := . ( @ % ;
ORA-06550: line 17, column 7:
PLS-00103: Encountered the symbol "OFF" when expecting one of the following:

   := . ( @ % ;
The symbol ":=" was substituted for "OFF" to continue.


What am I doing wrong?
How about a slightly different approach?

Put that code into a .SQL file, let's call it p.sql:

spool p.txt

SELECT /*csv*/
  TO_CHAR(SYSDATE,'YYYY-MM-DD HH24:MI:SS') AS "CurrentDTM",
  df.tablespace_name AS "Tablespace Name",
  SUBSTR(df.file_name,INSTR(df.file_name,'RMS_'),20) AS "File Name",
  Round(df.bytes/1073741824,2) AS "Size (GB)",
  decode(e.used_bytes,NULL,0,Round(e.used_bytes/1073741824,2)) AS "Used (GB)",
  decode(f.free_bytes,NULL,0,Round(f.free_bytes/1073741824,2)) AS "Free (GB)",
  decode(e.used_bytes,NULL,0,Round((e.used_bytes/df.bytes)*100,1)) AS "% Used"
FROM DBA_DATA_FILES DF,
     (SELECT file_id,sum(bytes) used_bytes FROM dba_extents GROUP by file_id) E,
     (SELECT sum(bytes) free_bytes,file_id FROM dba_free_space GROUP BY file_id) f
WHERE e.file_id (+) = df.file_id
  AND df.file_id  = f.file_id (+)
  AND df.tablespace_name = 'RMS_DATA'
ORDER BY df.tablespace_name,df.file_name;

spool off
exit




I'm on MS Windows, so - create a .BAT file; let's call it p.bat:

sqlplus sys/pwd as sysdba @p.sql




Finally, use your operating system scheduling program (Task Manager on my Windows) and schedule p.bat to run as often as you want, e.g. 


let it run daily at 22:00
action = Start a program (navigate to p.bat)
optionally (I'd suggest you to use it), set "Start in" directory


Should work; at least, works for me.