Oracle SQL Plus不考虑文件名中的特殊字符

There are files that contain special characters (i.e. $, #) in the name, and SQL Plus cannot find them.

$ sqlplus -L user/password@host:port/SID @/path/to/file/create$spec_view.sql

SQL*Plus: Release 12.2.0.1.0 Production on Thu Dec 13 17:29:54 2018

Copyright (c) 1982, 2016, Oracle. All rights reserved.

Last Successful login time: Thu Dec 13 2018 17:29:16 +03:00

Connected to:
Oracle Database 12c Enterprise Edition Release 12.2.0.1.0 - 64bit Production

SP2-0310: unable to open file "/path/to/file/create$spec_view.sql
SQL> Disconnected from Oracle Database 12c Enterprise Edition Release 12.2.0.1.0 - 64bit Production

实际上，这是Linux如何处理文件的问题。美元字符表示一个变量。这在将文件传递给程序时会导致问题。你会发现你可以在Windows中很好地阅读它 (19.3之前; 从这个版本中，你不能再读取名称为 $ 的Windows文件)。

所以这里真正的解决方案是坚持字母数字字符，句号，下划线和连字符 (虽然不是在开始!) 为您的文件名。如果您使用其他特殊字符，则可能会遇到其他程序的问题。

如果您绝对必须拥有一美元，则可以使用eschar变量，如MOS note 761384.1中所述:

SQL*Plus 10g rel2 or 11g in Unix/Linux displays an error trying to create or read a file that includes a special character like the dollar sign in the name, ie, test$.lst

-- Steps To Reproduce:

SQL> spool test$.lst
SP2-0332: Cannot create spool file.

Or

SQL>start /tmp/test$.lst
SP2-0310: unable to open file "/tmp/test$.lst"

-- Business Impact:

Scripting the extraction of DDL files from the database, but some user objects have "$" in the name and it causes the SPOOL command to fail. In other cases, it is an application requirement having files including a $ symbol.

-- What is working:

Scripts work on MS Windows platform.
Cause

1) BUG:11985886 'SP2-310 unable to open file error when file name ending with "$"' explains when the I/O calls were changed in later versions to use RDBMS Core Functions instead of OS calls, it impacted the way the special characters were handled.

2) This issue is already fixed in Patch Set 10.2.0.4. A new ESCCHAR variable is included there and in SQL*Plus 11g:

http://download.oracle.com/docs/cd/B28359_01/server.111/b31189/ch12040.htm#sthref2074
SQL*Plus® User's Guide and Reference
Release 11.1

SET ESCCHAR {@ | ? | % | $ | OFF}

Specifies a character to be escaped and not interpreted when used in a file name for the SPOOL, START, @, RUN and EDIT commands.
Solution

1) If you are using SQL*Plus 10gR2, apply Patch Set 10.2.0.4, where this bug is fixed.

a) Go to My Oracle Support (Metalink) -> Patches -> Simple Search -> Patch Number: 6810189

Review the readme and test it before applying on production environment. It is the database patchset, but can be applied on the Database Client if just SQL*Plus is installed in your ORACLE_HOME.

b) The new variable should be recognized in 10.2.0.4. Test as follows:

SQL> SET ESCCHAR $
SQL> spool /tmp/test$.lst

2) If you are in SQL*Plus 11g release, the variable ESCCHAR to escape the especial characters will be recognized without patches. When user switch on the ESCCHAR, then that character will not be translated in the filename:

SET ESCCHAR [@|?|$|OFF] - The default is OFF

Examples

a) Spooling:

SQL>SET ESCCHAR $
SQL> spool /tmp/test$.lst
SQL> spool off

b) Reading a file:

SQL>SET ESCCHAR $
SQL> start /tmp/myquery$.lst