Recently I ran into a problem were executing a SQL script where I received an SP2-0768: Illegal SPOOL command.
SQL> @test.sql
SP2-0768: Illegal SPOOL command
Usage: SPOOL { <file> | OFF | OUT }
where <file> is file_name[.ext] [CRE[ATE]|REP[LACE]|APP[END]]
SQL>
Looking at file in VI and the source of the problem is obvious. The file had end of line markers for DOS/Windows and not Unix.
spool test.txt^M^Mset timing on^Mset echo on^M^Mselect sysdate from dual;^M^Mspool off^M^Mexit^M ~ ~ ~ "test.sql" [noeol] 1L, 86C
While in VI issue the the following command :%s/^M/^M/g . Note in order to properly provide the ^M character you will need to do Ctrl-V followed by Ctrl-M.
spool test.txt^M^Mset timing on^Mset echo on^M^Mselect sysdate from dual;^M^Mspool off^M^Mexit^M ~ ~ ~ ~ ~ ~ ~ ~ ~ :%s/^M/^M/g
Once you hit return the end of line markers will be converted to Unix end of lines.
spool test.txt set timing on set echo on select sysdate from dual; spool off exit ~ ~ ~ 10 substitutions on 1 line 11,0-1 All
Save the file and re-run the sql script in SQL*Plus and the error will be gone.
SQL> @test.sql SQL> SQL> select sysdate from dual; SYSDATE --------- 13-OCT-12 Elapsed: 00:00:00.01 SQL> SQL> spool off SQL> SQL> exit Disconnected from Oracle Database 11g Enterprise Edition Release 11.2.0.3.0 - Production With the Partitioning, OLAP, Data Mining and Real Application Testing options [ejenkinson@wrath ~]$