ORA-00933: SQL 命令未正确结束
声明：本页面是StackOverFlow热门问题的中英对照翻译，遵循CC BY-SA 4.0协议，如果您需要使用它，必须同样遵循CC BY-SA许可，注明原文地址和作者信息，同时你必须将它归于原作者（不是我）：StackOverFlow
原文地址: http://stackoverflow.com/questions/72151/
Warning: these are provided under cc-by-sa 4.0 license. You are free to use/share it, But you must attribute it to the original authors (not me):
StackOverFlow
ORA-00933: SQL command not properly ended
提问by Steve Horn
I'm using OLEDB provider for ADO.Net connecting to an Oracle database. In my loop, I am doing an insert:
我将 OLEDB 提供程序用于连接到 Oracle 数据库的 ADO.Net。在我的循环中，我正在做一个插入：
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?
我究竟做错了什么？
采纳答案by massimogentilini
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.
对我来说，您似乎;在两个语句之间缺少一个：
尝试添加并让我们知道。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);;
回答by taranjeet
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: invalid character。好的，您认为一个 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: invalid character。
The solution to this problem is to wrap your 2 Oracle SQL statements with a BEGINand END;syntax, for example:
解决这个问题的方法是将你的2条Oracle SQL语句用aBEGIN和END;语法包裹起来，例如：
sql = "BEGIN DELETE FROM iphone_applications WHERE appid = 1; DELETE FROM iphone_applications WHERE appid = 2; END;"
礼貌：http: //www.lazyasscoder.com/Article.aspx?id=89&title =ora-00911%3A+invalid+character+when+executing+multiple+Oracle+SQL+statements
回答by CrackMonkeys4Hire
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 个语句，而不是尝试合并为一个语句，因为您不能使用分号。
回答by metadave
semi colon after the first insert?
第一次插入后的分号？
回答by ShoeLace
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;
回答by Steve Horn
In my loop I was not re-initializing my StringBuilder ...thus the multiple insert statement I posted.
在我的循环中，我没有重新初始化我的 StringBuilder ...因此我发布了多个插入语句。
Thanks for your help anyway!!
还是要谢谢你的帮助！！
回答by Jon Ericson
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.
除了分号问题，我强烈建议您查看绑定变量。不使用它们可能会导致数据库性能问题。代码也往往更简洁。
回答by stevechol
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 日期格式对英国/美国都有效，如果 Oracle DB 设置为英国日期格式，则第二次插入无效，我意识到您已经使用了 TO_DATE 函数，但我没有看别的...
回答by stevechol
Is the semicolon needed from OLE_DB ? It's not needed from most API's ?
OLE_DB 是否需要分号？大多数 API 不需要它？
回答by Mike Dimmick
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。
