How to generate an output file directly into my local machine when running a spool script using Oracle sqldeveloper?

Question

I am running a spool script using sqldeveloper. I have specified the path in my local to save the spool output file but its not saving. Also, in the sqldeveloper, the script output contains only the select statements and the query results are displayed on separate tabs. I have over 100 select statements in one script and hence saving each query result separately is not feasible.

I have included a sample stub from my spool script.

accept CRN PROMPT 'Enter the first detail ==> '
accept BRN PROMPT 'Enter the second detail ==> '
set head on
set array 1
set linesize 10000
set pagesize 50000
set long 10000
set echo on
set trimspool on
set colsep ';'
set numformat 9999999999999999.99
​
column filename new_val filename
​
select '&EVENT'||'_'||'&CRN'||'_SPOOL'||'.SPL' filename from dual;
​
Col ADDRESS1 NoPrint
Col ADDRESS2 NoPrint
Col ADDRESS_LINE1 NoPrint
Col ADDRESS_LINE2 NoPrint
Col CUST_NAME1 NoPrint​
​
SPOOL C:\Spool\Folder\
​
SELECT * FROM xxx;
SELECT * FROM yyy WHERE abc = '&CRN' ;
SELECT * FROM eyu WHERE rty='&CRN' ORDER BY 2 DESC;
​
set echo off
spool off

I tried running the above spool and as mentioned earlier the file is not getting generated. Request someone to provide a solution to this question:

Is it possible to somehow enable the script output to incorporate the query results all in one tab or to save the output file directly in my local machine?

Answer 1

You are spooling to a directory and not to a file.

SPOOL C:\Spool\Folder\

Given that you are generating a filename variable you probably want:

SPOOL C:\Spool\Folder\&filename

You could probably simplify the script to skip generating the filename variable and use:

SPOOL C:\Spool\Folder\&EVENT._&CRN._SPOOL.SPL