oracle 无法执行本机批量操作查询
声明：本页面是StackOverFlow热门问题的中英对照翻译，遵循CC BY-SA 4.0协议，如果您需要使用它，必须同样遵循CC BY-SA许可，注明原文地址和作者信息，同时你必须将它归于原作者（不是我）：StackOverFlow
原文地址: http://stackoverflow.com/questions/1709481/
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
could not execute native bulk manipulation query
提问by nandin
I am trying to implement a "if exists, update, otherwise, insert" data access method in NHibernate. My database is Oracle 10g.
我正在尝试在 NHibernate 中实现“如果存在，更新，否则，插入”数据访问方法。我的数据库是 Oracle 10g。
I am getting this "could not execute native bulk manipulation query" error when try to run this code, if I run the insert or update individualy, it works just fine.
尝试运行此代码时，出现“无法执行本机批量操作查询”错误，如果我单独运行插入或更新，则效果很好。
Thanks!
谢谢！
string sql = @"DECLARE
CntOfRow Number(10,0);
BEGIN
SELECT count(*)
INTO CntOfRow
FROM Table1
WHERE
QueID=:QueID
IF CntOfRow=0 THEN
INSERT INTO Table1 ...;
ELSE
UPDATE Table1 ... ;
END IF;
END;";
INHibernateSession session = NHibernateSessionManager.Instance.Session;
try
{
session.BeginTransaction();
ISQLQuery query = session.GetISession().CreateSQLQuery(sql.Replace(System.Environment.NewLine, " "));
query.SetParameter("QueID", queID);
query.ExecuteUpdate();
session.CommitTransaction();
}
catch (Exception ex)
{
session.RollbackTransaction();
throw;
}
采纳答案by Marius Burz
You seem to be missing an ;after the SELECT
你似乎缺少一个;后SELECT
This linkmight also be of interest to you.
您可能也对这个链接感兴趣。
As about inserting/updating, see MERGE statement. It works like this:
关于插入/更新，请参见 MERGE 语句。它是这样工作的：
MERGE INTO t1 dest
USING (SELECT 1 pk, 11 i FROM dual) src
ON (dest.pk = src.pk)
WHEN NOT MATCHED THEN INSERT (dest.pk, dest.i) VALUES (src.pk, src.i)
WHEN MATCHED THEN UPDATE SET dest.i = src.i;
Also see this topic
另请参阅此主题
回答by Tony Andrews
I don't know why you get that error, but you could try this simpler PL/SQL block instead:
我不知道你为什么会收到这个错误，但你可以试试这个更简单的 PL/SQL 块：
BEGIN
INSERT INTO Table1 ...;
EXCEPTION
WHEN DUP_VAL_ON_INDEX THEN
UPDATE Table1 ... ;
END;";
回答by Ferran Basora
Go to an Oracle client like Toad or SQL Editor and try to execute the procedure with the same parameters that you send through Hibernate.
转到像 Toad 或 SQL Editor 这样的 Oracle 客户端，并尝试使用您通过 Hibernate 发送的相同参数执行该过程。
In my case, Oracle was throwing an error like:
就我而言，Oracle 抛出了如下错误：
11:50:57 ORA-01403: no data found
The cause was a select inside of procedure/function. Then improve the procedure/function to catch these exceptions.
原因是过程/函数内部的选择。然后改进过程/函数以捕获这些异常。
