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
  • 55,518
  • 11
  • 116
  • 132
user1548815
  • 73
  • 2
  • 2
  • 8

7 Answers7

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
  • 1,071
  • 1
  • 9
  • 19
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
  • 40,127
  • 3
  • 30
  • 34
  • 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
  • 154
  • 2
  • 7
0

use as following

sqlplus -s system/passwd@mydb  "@my_sql";
Louis Barranqueiro
  • 10,058
  • 6
  • 42
  • 52
Naresh
  • 1
0

Context

FWIW: My sqlplus client (12.2.0.1.0) triggered the error when encountering a line-initial '#' character in the supplied sql script.

Remedy

Add a single blank as the first character of the offending line. If the data is part of plsql code or a sql statement, ie. inside a string literal spanning multiple lines, wrap the literal with the UNISTR(.) function and reference the crosshatch by \0023 (its utf8 hex code) (if db charset and national charset are al32utf8) or compose it from partial strings linked with ||chr(35)||.

Note

There may be a setting for sqlplus to change the observed behavior. I have not explored this route.

collapsar
  • 17,010
  • 4
  • 35
  • 61