#!/bin/ksh

sqlplus user@dbsid/password > 'my file with space' <b><<STR
</b>

select sysdate from dual;

STR

SQL> spool~my file
SQL>
SQL> spool~off

Problem Description 
------------------- 
On Windows it is not possible to spool files with space in the file name or 
spool files on directories with space in their names though the OS allows such 
names. 
When you try to spool to a directory with a space in it's name it gives: 
SQL>spool c:program filestest.lst 
SP2-0333: Illegal spool file name: "c:program filestest.lst" (bad character: ' ') 
When you try to spool a file with a space in it's name again it gives the same 
error: 
SQL>spool c:temptest emp.lst 
SP2-0333: Illegal spool file name: "c:temptest emp.lst" (bad character: ' ') 
This cannot be done even by using quotes around the file names. 

Solution Description 
-------------------- 
The following can be used as a workaround for this: 
If you want to spool a filename with space on a directory with a space in it's 
name then Spool it to some other directory (c:temp) and then move it to the 
required directory with the host command. 
spool c:temptest.lst 
select * from emp; 
spool off 
host move "c:temptest.lst" "c:program filestest emp.lst" 
If you want to spool a file on a directory with a space in it's name then 
specify a virtual drive for that directory through DOS prompt and then spool 
it to that drive. 
From the DOS prompt: 
subst g: "c:program files" 
and from SQL*Plus: 
spool g:test.lst 
select * from emp; 
spool off 
For removing the virtual directory mapping, from DOS prompt you can use: 
subst g: /d 

Explanation 
----------- 
This restriction is imposed by SQL*Plus because previously, SPOOL files would 
be created by mistake if the following commands were issued. 
SQL> spool out ; => Created spooling file OUT.LIS 
SQL> spool off ; => Created spooling file OFF.LIS 
These commands will now return: 
SQL> spool off ; 
Illegal spool file name: "off " (bad character: ' ') 
SQL> spool out ; 
Illegal spool file name: "out " (bad character: ' ') 
The provided solution works around this restriction by using the OS features 
which allow such names. 

#!/bin/ksh

sqlplus user@dbsid/password > 'my file with space' <<<b>STR</b>

select sysdate from dual;

STR