Using dollar sign in sqlplus spool file

Question

How to use file name with dollar sign (ie, '$') in unix like below

SQL> spool DIR$work.sql
SP2-0332: Cannot create spool file.

and i tried like below

SQL> spool DIR\$work.sql
SP2-0332: Cannot create spool file.
SQL> spool 'DIR\$work.sql'
SP2-0332: Cannot create spool file.
SQL> spool 'DIR$work.sql'
SP2-0332: Cannot create spool file.

I couldn't succeed in any way to create such file in oracle. I have oracle 11g version.

In windows sqlplus it works fine.

Answer 1

You can use the set escchar setting to stop Oracle interpreting the dollar sign:

SQL> show escchar
escchar OFF
SQL> spool /tmp/$work.sql
SP2-0332: Cannot create spool file.
SQL> set escchar $
SQL> spool /tmp/$work.sql
SQL>

You are now spooling to that file name.

SQL> select * from dual;

D
-
X

1 row selected.

SQL> spool off
SQL> exit
Disconnected from Oracle Database 11g Enterprise Edition Release 11.2.0.4.0 - 64bit Production
...

$ cat /tmp/\$work.sql
SQL> select * from dual;

D
-
X

1 row selected.

SQL> spool off

Also see My Oracle Support document 761384.1 for more information.