“ora-00911，无效字符串”的问题，为什么是";"引起的

wghxwl12

上传图片

Yong Huang

Many years ago I wrote about the anomaly of sqlplus edit window (see Item 7 of the following URL):

http://yong321.freeshell.org/computer/OracleIdiosyncrasies.html

I think it's related to the problem we're talking about here. But it's not exactly the same. Anyway, I rarely use edit command, other than these anomalies, it creates afiedt.buf in the directory where you launch sqlplus. If you don't clean up, the file becomes ugly litter everywhere on your filesystem.

Yong Huang

foxmile

ALTER SYSTEM SET CONTROL_FILES=
'H:\APP\ADMINISTRATOR\ORADATA\ORCL\CONTROL02.CTL',
'H:\APP\ADMINISTRATOR\ORADATA\ORCL\CONTROL03.CTL',
'H:\APP\ADMINISTRATOR\ORADATA\ORCL\CONTROL01.CTL',
'I:\Oracle back up\CONTROL04.CTL' SCOPE=SPFILE;
/

SQL> alter system set control_files=
  2  'D:\oracle\product\10.2.0\oradata\zero\CONTROL01.CTL,D:\oracle\product\10.2
.0\oradata\zero\CONTROL03.CTL,D:\oracle\product\10.2.0\oradata\zero\CONTROL03.CT
L,D:\oracle\product\10.2.0\oradata\test de\CONTROL04.CTL' scope=spfile;

楼主认真看看，这两个命令的区别。我这个绝对可以执行的。
你输入那么多的单引号做什么？ 人家那个命令指向一个字符串的。

[ 本帖最后由 foxmile 于 2008-11-9 23:22 编辑 ]

Yong Huang

wghxwl12's problem can be reproduced this way:

Type "select * from dual;" and hit Enter:

SQL> select * from dual;

D
-
X

Type edit, and in the popped-up Notepage window (assuming Windows), you have three lines (the 3rd line is just carriage return):
select * from dual
/

But here you append the semicolon ";" to the end of the SQL so it becomes:
select * from dual;
/

Exit Notepad and type "/":

SQL> ed
Wrote file afiedt.buf

  1* select * from dual;
SQL> /
select * from dual;
                  *
ERROR at line 1:
ORA-00911: invalid character


SQL>

The asterisk "*" points to ";". What should be done is either not use edit command or remember to not put ";" at the end of a SQL in the edit window. The edit window is not the same as a script file (say, myscript.sql) with these lines:
select * from dual;
/

When you run myscript.sql, sqlplus executes the SQL twice. But in an edit window, it throws the error ORA-911.

Yong Huang

sqysl

只要清楚，“;”号只是结束命令的标志，而不是命令必须的一部分就可以了，ORACLE命令里没有";"关键字，只是SQL*PLUS把他作为分隔命令与命令的分隔符号而已。如果可以定义，完全可以定义其他符号为命令分隔符，例如"!","#"等。

wghxwl12

再贴一段：

SQL> select * from dual;

DU
--
X

SQL> edit
已写入 file afiedt.buf

  1* select * from dual;(--这里手工加入";")
SQL> /
select * from dual;
                  *
第 1 行出现错误:
ORA-00911: 无效字符


SQL> edit
已写入 file afiedt.buf

  1* select * from dual
SQL> /

DU
--
X


SQL> select * from dual;;(--注意这里，有两个";",然后看下面报的错误)
select * from dual;
                  *
第 1 行出现错误:
ORA-00911: 无效字符

SQL> select * from dual;;
select * from dual;
                  *
第 1 行出现错误:
ORA-00911: 无效字符


SQL> edit
(--此时在弹出的编辑面板里面可以看到语句是加了一个";"的)
已写入 file afiedt.buf

  1* select * from dual;
SQL> /
select * from dual;
                  *
第 1 行出现错误:
ORA-00911: 无效字符


SQL> ;
  1* select * from dual;

SQL> select * from dual;

DU
--
X



总结(需要大家共同论证)
在plussql中进行编辑， 保存在afiedt.buf中的sql语句如果手工加入了";";然后再执行相当于加入了两个":"
在plussql环境进行edit编辑时不要手工加入";"只需换行加入"/"就可以了

另外，同意“孤独剑客”所讲的【“;”号只是结束命令的标志，而不是命令必须的一部分就可以了，ORACLE命令里没有";"关键字，只是SQL*PLUS把他作为分隔命令与命令的分隔符号而已】

[ 本帖最后由 wghxwl12 于 2008-11-12 11:09 编辑 ]

goodhawk

在windows 下的解决方案:

D:\>set NLS_LANG=AMERICAN_AMERICA.UTF8

其中的NLS_LANG要跟你的数据一致.

然后再 sqlplus 就ok了.

Yong Huang

原帖由 goodhawk 于 2008-11-12 03:32 发表
在windows 下的解决方案:

D:\>set NLS_LANG=AMERICAN_AMERICA.UTF8

其中的NLS_LANG要跟你的数据一致.

然后再 sqlplus 就ok了.

What problem are you referring to? The problem with sqlplus throwing ORA-911 error due to an extra comma in edit window?

Yong Huang