CODEWITHSUNDEEP
c#sqlplusoracle11gr2spool
Savy17
Savy17

Reputation: 1

Passing file name with spaces to SPOOL command using SQL Plus gives SP2-0768 Illegral SPOOL command error

My sql file contains

`SPOOL &1;
//sql code to execute
SPOOL OFF;`

The sql file is executed using SQL Plus and SQL Plus is being called from C# code using Process.Start... Code snipped

`var m_StartInfo = new ProcessStartInfo();
m_StartInfo.FileName = "SQLPLUS.EXE";
m_StartInfo.CreateNoWindow = true;
m_StartInfo.UseShellExecute = false;
m_StartInfo.Arguments = String.Format("{0}\"{1}\" \"{2}\"", connectionString, sqlfile, sqlLogFileName);
m_Process = Process.Start(m_StartInfo);
Other code.....`

It works fine and the sqlplus log is created fine at sqlLogFileName location. However if the sqlLogFileName has spaces in between (say like "C:\My Application\log.txt"), then the log file is not created, instead gives the error SP2-0768 Illegal SPOOL command on SQL Plus window

Any suggestion how to resolve this? I am using Oracle 11GR2

Upvotes: 0

Views: 7020

Answers (2)

Dzyann
Dzyann

Reputation: 5218

You just have to surround your file name with double quotes. Something like:

spool "Test with spaces.txt"

Or in your case with a parameter:

SPOOL "&1"

I think is best not to use spaces though, as @tvCa explained.

Side Note:

What StarPilot is refering to, I believe, is about the redirect a command output to a file, and that is why it didn't work when you tried to use it. For example in command prompt you would write:

dir > dir.txt

And that saves the output of dir inside dir.txt.

Upvotes: 2

tvCa
tvCa

Reputation: 816

Oracle software is designed to be used with directories not having whitespaces (which is an accepted standard in Linux/Unix, even though technically you can do otherwise). On Windows, things are different, but the Oracle software has the same idea : it does not like whitespaces. So, the fix is clear : spool to a directory without whitespaces. This is advice, anybody is free to take or not.

Upvotes: 0

Related Questions