如何编写一个 oracle 函数来更新不同模式上的表
声明：本页面是StackOverFlow热门问题的中英对照翻译，遵循CC BY-SA 4.0协议，如果您需要使用它，必须同样遵循CC BY-SA许可，注明原文地址和作者信息，同时你必须将它归于原作者（不是我）：StackOverFlow
原文地址: http://stackoverflow.com/questions/5487928/
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
how to write a oracle function to update tables on different schemas
提问by Alex Poole
hello i'm porting a wbsphere application to tomcat, i have to work on two database on the same server and i've integrated tomcat with atomikos transactionessential. This is my first project with jta, and the oracle dba has told me i don't need xa and two phase commit because the schemas are on the same server. So i've used the non-xa approach with atomikos. the following code on a single schema works fine (commit and rollback as expected):
你好，我正在将 wbsphere 应用程序移植到 tomcat，我必须在同一台服务器上处理两个数据库，并且我已经将 tomcat 与 atomikos transactionessential 集成在一起。这是我使用 jta 的第一个项目，oracle dba 告诉我我不需要 xa 和两阶段提交，因为模式在同一台服务器上。所以我在 atomikos 中使用了非 xa 方法。单个架构上的以下代码工作正常（按预期提交和回滚）：
utx.begin();
conn = //get connection
if (sAzione.equals("1"))
sql = "UPDATE parametri set valore =to_char(sysdate,'dd/mm/yyyy HH24:MI:ss') where id_parametri= 9 ";
//execute query
sql = "SELECT SEQ_LOTTO.nextval id FROM dual";
//other operations
sql = "INSERT INTO LOTTO (id_lotto, numero_lotto, id_area, id_stato_lavorazione, id_autore, id_tipo)";
sql = sql + " VALUES (" + id + ", " + numero + ", " + request.getParameter("idArea") + ",1,"+ session.getAttribute("id_anagrafica")+ "," + request.getParameter("idTipo") + ")";
//execute import and release connection
utx.commit();
in another place , the following oracle function gets called and try to change both schemas, and it returns the code 1 . I don't know pl-slq but it appears to me the return value would mean there has been an exception at first delete , yet the second delete gets executed and committed . Someone could explain me the meaning of this function ? below is the function and the code that calls it
在另一个地方，以下 oracle 函数被调用并尝试更改两个模式，它返回代码 1 。我不知道 pl-slq 但在我看来，返回值意味着第一次 delete 出现异常，但第二次 delete 被执行并提交。有人可以向我解释这个函数的含义吗？下面是函数和调用它的代码
create or replace FUNCTION FN_ELIMINA_RACC (idracc IN NUMBER, idlotto IN NUMBER)
RETURN NUMBER
IS
retvalue NUMBER (1);
BEGIN
retvalue := 1;
DELETE FROM npa_collaudo.documento_raccomandata
WHERE id_raccomandata = idracc;
retvalue := 2;
DELETE FROM raccomandata_out
WHERE id_racc_out = idracc;
retvalue := 3;
IF idlotto != 0
THEN
UPDATE lotto
SET numero_racc = numero_racc - 1
WHERE id_lotto = idlotto;
END IF;
retvalue := 0;
COMMIT;
RETURN retvalue;
EXCEPTION
WHEN OTHERS
THEN
RETURN retvalue;
END;
//the calling code
utx.begin();
//get connection
sql = "FN_ELIMINA_RACC(" + idRacc + ", " + idLotto + ");";
ret = connessioneDB.eseguiSP(sql);
if (!(ret == 0)){
throw new Exception("exception");
utx.commit();
//since it returns 1 an exception is raised and rollback gets called
thank you in advance for any help
预先感谢您的任何帮助
EDIT: investigating further into this (awful) code , and thanks to your answers ,i've found this into the infamous "eseguiSP" :
编辑：进一步调查这个（可怕的）代码，感谢你的回答，我在臭名昭著的“eseguiSP”中发现了这个：
//strSQL is "FN_ELIMINA_RACC(..."
DBOracle dbType = new DBOracle();
String SQL = "";
int retValue = 0;
SQL = " DECLARE ";
SQL = SQL + " ret NUMBER; ";
SQL = SQL + " BEGIN ";
SQL = SQL + " ret := " + strSQL;
SQL = SQL + " END; ";
try {
stmt = conn.prepareCall(SQL);
retValue = stmt.executeUpdate(SQL);
} catch (SQLException e) {
//retValue = false;
}
return retValue;
And i've changed it to:
我已经将其更改为：
c = ds.getConnection();
java.sql.CallableStatement cstmt = c.prepareCall("{?=call FN_ELIMINA_RACC(?,?)}");
cstmt.registerOutParameter(1,java.sql.Types.INTEGER);
cstmt.setInt(2, idRacc);
cstmt.setInt(3, idLotto);
cstmt.execute();
ret = cstmt.getInt(1);
now it works fine (or at least returns 0).Why the old piece of code always returned 1 even if it deleted records from raccomandata_out?
现在它工作正常（或至少返回 0）。为什么旧的代码段总是返回 1，即使它从 raccomandata_out 中删除了记录？
回答by Dave Costa
Since the function is returning 1, that would indicate that the first delete is throwing an exception. This causes control to be switched to the EXCEPTIONblock, which simply returns. None of the other code after the first delete should be executed at all.
由于该函数返回 1，这表明第一个删除正在引发异常。这会导致控制被切换到EXCEPTION块，它只是返回。第一次删除之后的任何其他代码都不应该被执行。
The exception handler is horrible, in that it catches any exception, discards it, and returns a flag value that tells you very little about what happened. It's only slightly better than WHEN OTHERS THEN NULL;. As this is written, you have no way of knowing what exception occurred. The exception handler should either be removed (so that the calling code can catch and deal with the exception in some way), or rewritten to at least log the actual exception message (SQLERRM) somehow.
异常处理程序很糟糕，因为它捕获任何异常，丢弃它，并返回一个标志值，该值很少告诉您发生了什么。它只比WHEN OTHERS THEN NULL;. 正如所写的那样，您无法知道发生了什么异常。应该删除异常处理程序（以便调用代码可以以某种方式捕获和处理异常），或者重写以至少以某种方式记录实际的异常消息 (SQLERRM)。
The most obvious guess is that the exception is being raised because the schema in which the code is executed does not have delete access to the table in the other schema. One Oracle quirk which might be relevant is that stored PL/SQL code (such as this function) cannot take advantage of access granted via a role. Any access to other schemas' objects must be granted directly to the user.
最明显的猜测是引发异常是因为执行代码的架构没有对另一个架构中的表的删除访问权限。一个可能相关的 Oracle 怪癖是存储的 PL/SQL 代码（例如此函数）无法利用通过角色授予的访问权限。对其他模式对象的任何访问都必须直接授予用户。
回答by Justin Cave
The exception handler in this procedure is not particularly useful. It is completely hiding the error message that Oracle is throwing. If you eliminate the exception handler entirely, what is the error stack?
此过程中的异常处理程序不是特别有用。它完全隐藏了 Oracle 抛出的错误消息。如果完全消除异常处理程序，错误堆栈是什么？
My guess is that the owner of the procedure doesn't have privileges to delete rows from the npa_collaudo.documento_raccomandatatable. But it's impossible to know that without knowing what exception is actually being raised.
我的猜测是该过程的所有者没有从npa_collaudo.documento_raccomandata表中删除行的权限。但是，如果不知道实际引发了什么异常，就不可能知道这一点。
回答by Alex Poole
How do you know the function is returning 1? The exception you're throwing isn't reporting the retvalue. The call itself may be broken - try removing the trailing ;from the sqlstring. Although you ought to get a more helpful exception from eseguiSP(sql)if that's the case, but it might be hidden elsewhere in your code (maybe something further up is adding something that makes it looks like a 1 was returned?); and neither delete should take effect, unless it's trying to treat it as two commands and only complaining when it sees the second is null. That sounds unlikely but you never know, so I'd try removing the semi-colon anyway.
你怎么知道函数返回 1？您抛出的异常未报告ret值。调用本身可能已损坏 - 尝试;从sql字符串中删除尾随。虽然eseguiSP(sql)如果是这种情况，你应该得到一个更有用的异常，但它可能隐藏在你的代码中的其他地方（也许更进一步的东西是添加一些使它看起来像 1 被返回的东西？）；并且这两个删除都不应该生效，除非它试图将它视为两个命令并且仅在它看到第二个为空时才抱怨。这听起来不太可能，但你永远不知道，所以无论如何我都会尝试删除分号。
Also, you should probably be using bind parameters for the call, not embedding the values in sql.
此外，您可能应该为调用使用绑定参数，而不是将值嵌入sql.
You also said rollback would be called on exception, and you have utx.commit(), but that's redundant with a commit in the function too.
您还说会在异常时调用回滚，并且您有utx.commit()，但这对于函数中的提交也是多余的。
