Dynamic Spool to File name
<p>I would like to extract the same query every week in sqlplus or SQLCL, maybe scheduling the task on windows server, spooling in a csv file with the name of the account code and the date.
I wrote the following script and it works but I've two problems.</p>

<pre><code>SET ECHO OFF
SET FEEDBACK OFF
SET VERIFY OFF
COL VPATH NEW_VALUE NEW_VPATH;
COL PRESIDIO NEW_VALUE NEW_PRESIDIO;
COL SEPARATORE NEW_VALUE NEW_SEPARATORE;
COL DATA NEW_VALUE NEW_DATA;
COL EXT NEW_VALUE NEW_EXT;
SET SQLFORMAT CSV;
SELECT 'C:\Shared_Folder\' VPATH, 'AO_GALLARATE' PRESIDIO,'_' SEPARATORE, TO_CHAR(CURRENT_DATE) DATA,'.CSV' EXT FROM DUAL;
SET FEEDBACK ON;
SET TERMOUT OFF;
SPOOL "&amp;NEW_VPATH&amp;NEW_PRESIDIO&amp;NEW_SEPARATORE&amp;NEW_DATA&amp;NEW_EXT";
SELECT /*CSV*/ SERIAL_NUMBER, DNS_HOST_NAME, IP_ADDRESS, MODEL_NAME, TOTAL_PAGE_COUNT - COLOR_PAGE_COUNT, COLOR_PAGE_COUNT, TRUNC(LAST_DATA_COLLECTION) FROM DEVICE WHERE ACCOUNT_CODE = '&amp;NEW_PRESIDIO' AND model_name LIKE '%MS410%';
ORDER BY SERIAL_NUMBER ASC;
SPOOL OFF;
</code></pre>

<ol>
<li>i get a prompt requesting to enter a value for all new_vpath, new_presidio.. at least for the first time. If i remove this part, i get the request when it's supposed to start spooling</li>
<li>If i use pl/sql, i can successfully set the variables, so no prompt appears, but spooling and query are not working. actually spooling says illegal file name and query is not working for missing "account_code" string in the query.</li>
</ol>

<p>Where and what I'm doing wrong?</p>