2

I am running this command in powershell:

sqlplus system/passwd@mydb  @my_sql

I have tried it with and without backticks and various other versions I found via Google. I keep getting an error when the command is passed off to sqlplus and have been unsucessful in finding the fix. Hopefully someone here can help out?

The error I get is:

Connected to:
Oracle Database 11g Enterprise Edition Release 11.2.0.1.0 - 64bit Production
With the Partitioning, OLAP, Data Mining and Real Application Testing options

SP2-0042: unknown command " ■@" - rest of line ignored.

So I am sucessfully connecting to the database but there is an extra character being passed to sqlplus in front of the '@'. " ■@" in notepad++ looks like " ¦@"

Aaron
  • 51,095
  • 11
  • 112
  • 124
user1548815
  • 73
  • 2
  • 2
  • 8

6 Answers6

2

If you created your SQL command file using a redirect (> or >>) in powershell - like:


myProgram > mySQL.out
and then run it like:
&sqlplus mypw/myuser@mydb.xyz.com "@mySQL.out"

Powershell may have saved the output file in UTF-16 format, which Sqlplus does not like. (You can confirm by creating the exact same file by hand and then comparing it - byte count will be off and in KDiff you'll get message to the effect that the text is equal, but the files are not binary equal).

To fix - you need to do two things: :

  1. Add some blank lines to the top of your SQL commands - Powershell will still write a BOM (Byte Order Mark) there and it looks like it's pretty hard to get it to avoid that - but sqlplus will just go by it, albeit giving an error - but will move on to the rest of your code OK.
  2. And then run this command in powershell before creating your file: $PSDefaultParameterValues['Out-File:Encoding'] = 'utf8'

See Changing PowerShell's default output encoding to UTF-8

I received this error:
SP2-0042: unknown command " ■S" - rest of line ignored.
and this fixed that - or at least I was able to run it. You can also just cut and past it from one window into another using Notepad++ and that will solve the BOM and encoding issue.

DJB55
  • 21
  • 4
1

Update Problem Solved. This turned out being "not seeing the trees through the forest". I have been using these sql scripts for several years without issue called from a bash script. When I tried converting the bash script to powershell and ran into issues I blamed it on powershell. However; it turned out there was something corrupt in the sql file itself. There were no obvious errors when looking at the file in notepad++ even with show all symbols clicked and it was ANSI format. I determined it was the sql file itself when I manually ran sqlplus from a cmd window I still had the same error I was getting with powershell. I rewrote the script and saved it and the problem was fixed. I should have manually ran the script on day one and I probably could have resolved sooner.

user1548815
  • 73
  • 2
  • 2
  • 8
1

I had the same problem. My issue was caused because the script file was saved as unicode. I don't know if this will help you or not, but here is how I fixed it:

Edit the script with notepad. Click File -> Save As. Change type from Unicode (or whatever) to ANSI, and save.

Uday Chauhan
  • 896
  • 1
  • 7
  • 18
0

A couple of suggestions

Try the invoke operator:

&sqlplus system/passwd@mydb @my_sql

Try start-process:

start-process -NoNewWindow -FilePath sqlplus -ArgumentList @"
system/passwd@mydb @my_sql
"@
Chad Miller
  • 37,167
  • 3
  • 27
  • 33
  • Update: I tried both methods suggested and the error still occurred. Interestingly I am seeing the same problem when running from a batch script. – user1548815 Aug 06 '12 at 13:29
0

I had typical problem. The message was: unknown command "and" - rest of line ignored. The reason was an empty string in code. e.g. select ... from ... where ... [empty string] and ... < here was an error message

Oleksii
  • 144
  • 2
  • 7
0

use as following

sqlplus -s system/passwd@mydb  "@my_sql";
Louis Barranqueiro
  • 9,390
  • 6
  • 39
  • 51
Naresh
  • 1