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

Question

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:

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.

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?

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.

Answer 1

have you tried escaping the white space?

Try:

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

Answer 2

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

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

Answer 3

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:

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...

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.

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

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:

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:

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

Then modify your build scripts thus:

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

Answer 4

According to this thread on 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).

Answer 5

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:

1. Rename the My Schema directory 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)

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

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