ORA-00933：SQL命令未正确结束

I'm using OLEDB provider for ADO.Net connecting to an Oracle database. In my loop, I am doing an insert:

我正在使用OLEDB提供程序将ADO.Net连接到Oracle数据库。在我的循环中,我正在做一个插入:

insert into ps_tl_compleave_tbl values('2626899', 0, TO_DATE('01/01/2002', 'MM/DD/YYYY'), 'LTKN', 'LTKN', '52', TO_DATE('01/01/2002', 'MM/DD/YYYY'), 16.000000, 24.000)insert into ps_tl_compleave_tbl values('4327142', 0, TO_DATE('03/23/2002', 'MM/DD/YYYY'), 'LTKN', 'LTKN', '51', TO_DATE('03/23/2002', 'MM/DD/YYYY'), 0.000000, 0.000)

The first insert succeeds but the second one gives an error:

第一个插入成功但第二个插入错误:

ORA-00933: SQL command not properly ended

What am I doing wrong?

我究竟做错了什么?

11 个解决方案

#1

To me it seems you're missing a ; between the two statements:
insert into ps_tl_compleave_tbl values('2626899', 0, TO_DATE('01/01/2002', 'MM/DD/YYYY'), 'LTKN', 'LTKN', '52', TO_DATE('01/01/2002', 'MM/DD/YYYY'), 16.000000, 24.000)
;
insert into ps_tl_compleave_tbl values('4327142', 0, TO_DATE('03/23/2002', 'MM/DD/YYYY'), 'LTKN', 'LTKN', '51', TO_DATE('03/23/2002', 'MM/DD/YYYY'), 0.000000, 0.000)
;
Try adding the ; and let us know.

对我而言,似乎你错过了一个;两个语句之间:插入ps_tl_compleave_tbl值('2626899',0,TO_DATE('01 / 01/2002','MM / DD / YYYY'),'LTKN','LTKN','52',TO_DATE(' 01/01/2002','MM / DD / YYYY'),16.000000,24.000);插入ps_tl_compleave_tbl值('4327142',0,TO_DATE('03 / 23/2002','MM / DD / YYYY'),'LTKN','LTKN','51',TO_DATE('03 / 23/2002 ','MM / DD / YYYY'),0.000000,0.000);尝试添加;让我们知道。

#2

In .net, when we try to execute a single Oracle SQL statement with a semicolon at the end. The result will be an oracle error: ora-00911: invalid character. OK, you figure that one SQL statement doesn't need the semicolon, but what about executing 2 SQL statement in one string for example:

在.net中,当我们尝试在末尾执行带分号的单个Oracle SQL语句时。结果将是oracle错误:ora-00911:无效字符。好吧,你认为一个SQL语句不需要分号,但是如果在一个字符串中执行2个SQL语句,例如:

Dim db As Database = DatabaseFactory.CreateDatabase("db")
Dim cmd As System.Data.Common.DbCommand
Dim sql As String = ""

sql = "DELETE FROM iphone_applications WHERE appid = 1; DELETE FROM iphone_applications WHERE appid = 2; "

cmd = db.GetSqlStringCommand(sql)
db.ExecuteNonQuery(cmd)

The code above will give you the same Oracle error: ora-00911: invalid character.

上面的代码会给你相同的Oracle错误:ora-00911:无效字符。

The solution to this problem is to wrap your 2 Oracle SQL statements with a BEGIN and END; syntax, for example:

这个问题的解决方案是使用BEGIN和END包装2个Oracle SQL语句;语法,例如:

sql = "BEGIN DELETE FROM iphone_applications WHERE appid = 1; DELETE FROM iphone_applications WHERE appid = 2; END;"

Courtesy: http://www.lazyasscoder.com/Article.aspx?id=89&title=ora-00911%3A+invalid+character+when+executing+multiple+Oracle+SQL+statements

#3

In Oracle the semi-colon ';' is only used in sqlplus. When you are using ODBC/JDBC, OLEDB, etc you don't put a semi-colon at the end of your statement. In the above case you are actually executing 2 different statements so the best way to handle the problem is use 2 statements instead of trying to combine into a single statement since you can't use the semi-colon.

在Oracle中的分号';'仅用于sqlplus。当您使用ODBC / JDBC,OLEDB等时,不要在语句的末尾加上分号。在上面的例子中,您实际上正在执行2个不同的语句,因此处理问题的最佳方法是使用2个语句,而不是尝试组合成单个语句,因为您不能使用分号。

#4

semi colon after the first insert?

第一次插入后的半结肠?

#5

Oracle SQL uses a semi-colon ; as its end of statement marker.

Oracle SQL使用分号;作为语句结束标记。

you will need to add the ; after bother insert statments.

你需要添加;在插入声明之后。

NB: that also assumes ADODB will allow 2 inserts in a single call.

注意:这也假设ADODB将在一次通话中允许2个插入。

the alternative might be to wrap both calls in a block,

替代方案可能是将两个调用都包装在一个块中,

BEGIN
      insert (...) into (...);
      insert (...) into (...);
END;

#6

In my loop I was not re-initializing my StringBuilder ...thus the multiple insert statement I posted.

在我的循环中,我没有重新初始化我的StringBuilder ...因此我发布了多个插入语句。

Thanks for your help anyway!!

还是要谢谢你的帮助!!

#7

It's a long shot but in the first insert the sql date format is valid for both uk/us, the second insert is invalid if the Oracle DB is setup for UK date format, I realise you have used the TO_DATE function but I don't see anything else ...

这是一个很长的镜头,但在第一个插入中,sql日期格式对于uk / us都有效,如果Oracle DB设置为UK日期格式,则第二个插入无效,我意识到你已经使用了TO_DATE函数但我没有看到别的......

#8

Is the semicolon needed from OLE_DB ? It's not needed from most API's ?

OLE_DB需要分号吗?大多数API都不需要它?

#9

The ADO.NET OLE DB provider is for generic data access where you don't have a specific provider for your database. Use OracleConnection et al in preference to OleDbConnection for an Oracle database connection.

ADO.NET OLE DB提供程序用于通用数据访问,您没有数据库的特定提供程序。对Oracle数据库连接使用OracleConnection等优先于OleDbConnection。

#10

In addition to the semicolon problem, I strongly recommend you look into bind variables. Failing to use them can cause database performance problems down the road. The code also tends to be cleaner.

除了分号问题,我强烈建议你研究绑定变量。未能使用它们可能会导致数据库性能问题。代码也趋于清晰。

#11

The issue may be that you have a parameter variable that is null being inserted into the query. That was what my problem was. Once I gave the parameter a default value of empty string, it worked.

问题可能是您有一个插入查询的null参数变量。这就是我的问题所在。一旦我给参数一个默认值为空字符串,它就可以了。

#1

#2

Dim db As Database = DatabaseFactory.CreateDatabase("db")
Dim cmd As System.Data.Common.DbCommand
Dim sql As String = ""

sql = "DELETE FROM iphone_applications WHERE appid = 1; DELETE FROM iphone_applications WHERE appid = 2; "

cmd = db.GetSqlStringCommand(sql)
db.ExecuteNonQuery(cmd)