oracle sqlplus在@命令的路径中有空格的问题

problem with oracle sqlplus with whitespace in the path of the @ command

提问by Stephen

I'm running Oracle 11g on Linux and I'm trying to run a script which will create my database. This script runs fine on windows, but when I test it on Linux, I get the following error:

我在 Linux 上运行 Oracle 11g，我正在尝试运行一个脚本来创建我的数据库。此脚本在 Windows 上运行良好，但在 Linux 上测试时，出现以下错误：

SP2-0556: Invalid File Name

The problem may be that the path to the file name has a space in it. I'm going to simplify the problem down to one of the many commands I run in the file to make it simple. The sample command I'm trying to run looks like this:

问题可能是文件名的路径中有空格。我将把问题简化为我在文件中运行的众多命令之一，以使其变得简单。我尝试运行的示例命令如下所示：

sqlplus [uname]/[pw] @'../database/My Schema/create_sequence.sql'

the create_sequence.sql file has two simple create sequence commands that run fine by themselves. I strongly suspect it is due to the white space because when I change the directory name from My Schema to MySchema and alter the above sqlplus command accordingly, the script runs fine.

create_sequence.sql 文件有两个简单的创建序列命令，它们自己运行良好。我强烈怀疑这是由于空格造成的，因为当我将目录名称从 My Schema 更改为 MySchema 并相应地更改上述 sqlplus 命令时，脚本运行良好。

Like I said, this script works in windows with the spaces, but not in Linux. I suspect spaces may not be supported, but I was wondering if anyone knew any different or it there is a work-around?

就像我说的，这个脚本在带有空格的 Windows 中工作，但在 Linux 中不起作用。我怀疑可能不支持空间，但我想知道是否有人知道任何不同或有解决方法？

side note: running a command like:

旁注：运行如下命令：

more ../database/My\ Schema/create_sequence.sql

or

或者

more "../database/My Schema/create_sequence.sql" 

prints the contents of the file to the console as you would expect. So, I think this is sqlplus (and linux) specific.

如您所料，将文件内容打印到控制台。所以，我认为这是 sqlplus（和 linux）特定的。

采纳答案by Mark Roddy

I connected to one of my Linux boxes and was pretty easily able to reproduce this issue. There doesn't seem to be any way that I can find to execute the file with the '@' option from the command line so I think you're left with the following options for work arounds:

我连接到我的一个 Linux 机器上，并且很容易重现这个问题。我似乎找不到任何方法可以从命令行使用“@”选项执行文件，因此我认为您可以使用以下解决方法：

1. Rename the My Schemadirectory to no longer have a space in it (as well as updating all other scripts that reference it
2. Execute the file from the directory in which it resides (I confirmed that this works, see below)
3. Send the file into sqlplus via stdin (see below)

1. 将My Schema目录重命名为不再有空格（以及更新所有其他引用它的脚本
2. 从它所在的目录执行文件（我确认这有效，见下文）
3. 通过标准输入将文件发送到 sqlplus（见下文）

You should also file a report with Oracle support as there may be a simple fix that they can provide.

您还应该向 Oracle 支持提交报告，因为他们可能会提供一个简单的修复程序。

Example Commands:

示例命令：

From Directory

从目录

cd ../database/My\ Schema
sqlplus [uname]/[pw] @create_sequence.sql

Via stdin

通过标准输入

sqlplus [uname]/[pw] < ../database/My\ Schema/create_sequence.sql

回答by DCookie

Well, if this is a Linux issue (see my comment on your question - it works fine on Solaris), you may have to try something along the lines of:

好吧，如果这是一个 Linux 问题（请参阅我对您的问题的评论 - 它在 Solaris 上运行良好），您可能需要尝试以下方法：

sqlplus [uname]/[pw] < '../database/My Schema/create_sequence.sql'

You run into problems if you're trying to pass parameters to your sql script, however...

如果您尝试将参数传递给 sql 脚本，则会遇到问题，但是...

EDIT: There seems to be a Metalink issue raised for a very similar problem: "Bug 7150873 SQL scripts with filename containing spaces results in SP2-0556". It is listed as affecting 10.2.0.4 and 11.1. It is supposedly fixed in 10.2.0.5 and 11.2, neither which are available yet. It does say it's a generic issue affecting most/all platforms, so I don't know if this is your problem or not.

编辑：对于一个非常相似的问题，似乎有一个 Metalink 问题：“错误 7150873 SQL 脚本，文件名包含空格导致 SP2-0556”。它被列为影响 10.2.0.4 和 11.1。它应该在 10.2.0.5 和 11.2 中得到修复，但两者都还没有。它确实说这是影响大多数/所有平台的通用问题，所以我不知道这是否是您的问题。

The specific text of the issue: "The SQLPLUS START command fails to execute SQL scripts which have a space in the filename."

问题的具体文本：“SQLPLUS START 命令无法执行文件名中有空格的 SQL 脚本。”

Just for grins, what happens if you do the following:

只是为了咧嘴笑，如果您执行以下操作会发生什么：

sqlplus [uname]/[pw]
start '../database/My Schema/create_sequence.sql'

EDIT2: I don't know if modifying your scripts wholesale is feasible or not, but a workaround might be:

EDIT2：我不知道批量修改脚本是否可行，但解决方法可能是：

cp '../database/My Schema/file2run.sql' ./temp.sql
sqlplus [uname]/[pw] @temp.sql
rm ./temp.sql

You would need to wrap each sqlplus call this way. Another option would be to create a shell script, say with a name of mysqlplus.sh:

您需要以这种方式包装每个 sqlplus 调用。另一种选择是创建一个 shell 脚本，比如名称为 mysqlplus.sh：

#!/bin/sh
cp  ./temp$$
sqlplus  @
rm ./temp$$

Then modify your build scripts thus:

然后修改您的构建脚本：

mysqlplus.sh [uname]/[pw] '../database/My Schema/create_sequence.sql'

回答by Mark Roddy

According to this threadon the OTN site, SP2-0556 can be caused by invalid white space characters in the file that is being executed. Likely the Linux version of SQL-Plus doesn't know how to deal with Windows newline character(s). Try deleting the existing file and recreating it with your desired commands (you said there are only 2 DDL commands so it should be easy).

根据OTN 站点上的这个线程，SP2-0556 可能是由正在执行的文件中的无效空白字符引起的。可能 Linux 版本的 SQL-Plus 不知道如何处理 Windows 换行符。尝试删除现有文件并使用您想要的命令重新创建它（您说只有 2 个 DDL 命令，所以应该很容易）。

回答by Deep

If you put quotes around path, it works:

如果你在路径周围加上引号，它会起作用：

SQL > START "C:\Documents and Settings\Administrator\demobuild.sql" 

This does notwork:

这并不能正常工作：

SQL > START C:\Documents and Settings\Administrator\demobuild.sql 

回答by John

I know it's an old question but I just ran into the same situation and after a lot of tries I made it work and I want to share my solution, it was simply put the path into quotes like this:

我知道这是一个老问题，但我遇到了同样的情况，经过多次尝试，我使它起作用，我想分享我的解决方案，它只是将路径放入这样的引号中：

@"C:/Users/john/AppData/Roaming/SQL Developer/test.sql";

@"C:/Users/john/AppData/Roaming/SQL Developer/test.sql";

回答by northpole

have you tried escaping the white space?

你有没有试过逃避空白？

Try:

尝试：

sqlplus [uname]/[pw] @'../database/My\ Schema/create_sequence.sql'

回答by Mark Roddy

If you're working with this under linux you can escape the space with a '\' character like such:

如果您在 linux 下使用它，您可以使用 '\' 字符转义空格，如下所示：

sqlplus [uname]/[pw] @../database/My\ Schema/create_sequence.sql