如何编写一个oracle函数来更新不同模式上的表
你好 我正在将 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();
在另一个地方，调用以下 oracle 函数并尝试更改两个模式，它返回代码 1 。我不知道 pl-slq 但在我看来返回值意味着第一次删除时出现异常，但第二次删除被执行并提交。有人可以解释一下这个函数的含义吗？下面是函数和调用它的代码
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
提前感谢您的帮助
编辑：进一步调查这个（可怕的）代码，并且感谢您的回答，我在臭名昭著的“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;
而且我已经将其更改为：
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);
现在它工作正常（或至少返回 0）。为什么旧代码总是返回 1，即使它从 raccomandata_out 中删除了记录？
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):
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
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" :
//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?
如果你对这篇内容有疑问，欢迎到本站社区发帖提问 参与讨论，获取更多帮助，或者扫码二维码加入 Web 技术交流群。
绑定邮箱获取回复消息
由于您还没有绑定你的真实邮箱，如果其他用户或者作者回复了您的评论，将不能在第一时间通知您！
发布评论
评论（3）
由于该函数返回 1，这表明第一次删除引发了异常。这会导致控制权切换到 EXCEPTION 块，该块会简单地返回。第一次删除后的其他代码根本不应该执行。
异常处理程序很糟糕，因为它捕获任何异常，丢弃它，并返回一个标志值，该标志值几乎无法告诉您发生了什么。它只比
WHEN OTHERS THEN NULL;稍好一些。在编写本文时，您无法知道发生了什么异常。异常处理程序应该被删除（以便调用代码可以以某种方式捕获和处理异常），或者重写以至少以某种方式记录实际的异常消息（SQLERRM）。最明显的猜测是引发异常是因为执行代码的模式没有对其他模式中的表的删除访问权限。一个可能相关的 Oracle 怪癖是存储的 PL/SQL 代码（例如此函数）无法利用通过角色授予的访问权限。对其他模式对象的任何访问都必须直接授予用户。
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.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.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 抛出的错误消息。如果完全消除异常处理程序，错误堆栈是什么？
我的猜测是该过程的所有者没有从
npa_collaudo.documento_raccomandata表中删除行的权限。但如果不知道实际引发了什么异常，就不可能知道这一点。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?
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.你怎么知道函数返回1？您抛出的异常不报告
ret值。调用本身可能已损坏 - 尝试从sql字符串中删除尾随的;。虽然如果是这种情况，您应该从 eseguiSP(sql) 获得更有用的异常，但它可能隐藏在代码中的其他位置（也许进一步添加一些东西，使其看起来像 1被退回了？）；并且两个删除都不会生效，除非它试图将其视为两个命令并且仅在看到第二个命令为空时才抱怨。这听起来不太可能，但你永远不知道，所以无论如何我都会尝试删除分号。另外，您可能应该使用绑定参数进行调用，而不是将值嵌入
sql中。您还说回滚将在异常时调用，并且您有 utx.commit() ，但这对于函数中的提交也是多余的。
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 thesqlstring. Although you ought to get a more helpful exception fromeseguiSP(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.Also, you should probably be using bind parameters for the call, not embedding the values in
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.