oracle PL/SQL 异常和 Java 程序
声明：本页面是StackOverFlow热门问题的中英对照翻译，遵循CC BY-SA 4.0协议，如果您需要使用它，必须同样遵循CC BY-SA许可，注明原文地址和作者信息，同时你必须将它归于原作者（不是我）：StackOverFlow
原文地址: http://stackoverflow.com/questions/2441915/
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
PL/SQL exception and Java programs
提问by edwards
Business logic is coded in pl/sql packages procedures and functions. Java programs call pl/sql packages procedures and functions to do database work.
业务逻辑编码在 pl/sql 程序包和函数中。Java 程序调用 pl/sql 包过程和函数来完成数据库工作。
pl/sql programs store exceptions into Oracle tables whenever an exception is raised.
每当出现异常时，pl/sql 程序都会将异常存储到 Oracle 表中。
How would my java programs get the exceptions since the exception instead of being propagated from pl/sql to java is getting persisted to a oracle table and the procs/functions just return 1 or 0.
我的 java 程序如何获得异常，因为异常而不是从 pl/sql 传播到 java 被持久化到 oracle 表中，而 procs/functions 只返回 1 或 0。
Sorry folks i should have added this constraint much earlier and avoided this confusion. As with many legacy projects we don't have the freedom to modify the stored procedures.
抱歉，我应该更早地添加此约束并避免这种混淆。与许多遗留项目一样，我们没有修改存储过程的自由。
回答by duffymo
java.sql.CallableStatement throws java.sql.SQLException. If your stored proc throws an exception, your Java code will know about it. The code that calls the stored proc will have to handle this exception.
java.sql.CallableStatement 抛出 java.sql.SQLException。如果您的存储过程抛出异常，您的 Java 代码就会知道它。调用存储过程的代码必须处理这个异常。
You have a choice: you can either have the stored proc throw the exception or have the Java code check the return value and query the table for the exception if the error code is returned.
您有一个选择：您可以让存储过程抛出异常，或者让 Java 代码检查返回值并在返回错误代码时查询表中的异常。
But the exception isn't "lost" either way. You get it from the JVM or the table.
但无论哪种方式，例外都不会“丢失”。您可以从 JVM 或表中获取它。
I vote for the JVM because it's easier. Less PL/SQL code, less Java code.
我投票支持 JVM，因为它更容易。更少的 PL/SQL 代码，更少的 Java 代码。
回答by YogoZuno
Assuming you can't change the PLSQL code, you'll have to monitor the table. And of course, that will only work if the error table stores some sort of session or use identifier.
假设您无法更改 PLSQL 代码，您将不得不监视该表。当然，这仅在错误表存储某种会话或使用标识符时才有效。
回答by Gary Myers
"is getting persisted to a oracle table" You could create a trigger on that table that rejects the insert. For example, if the error table contains an 'ERROR_DESCRIPTION' column, you could have a BEFORE INSERT ON error_table FOR EACH ROW trigger which does a RAISE_APPLICATION_ERROR(-20001,:NEW.ERROR_DESCRIPTION)
“正在持久化到 oracle 表” 您可以在该表上创建一个拒绝插入的触发器。例如，如果错误表包含一个 'ERROR_DESCRIPTION' 列，您可以有一个 BEFORE INSERT ON error_table FOR EACH ROW 触发器，它执行 RAISE_APPLICATION_ERROR(-20001,:NEW.ERROR_DESCRIPTION)
When the PL/SQL code goes to log the error, that will fail with the replacement error and that will, if you are lucky, get propogated to the Java layer.
当 PL/SQL 代码去记录错误时，它将失败并出现替换错误，如果幸运的话，它将被传播到 Java 层。
It is an ugly hack, but if you truly can't change the code, it may work.
这是一个丑陋的黑客，但如果你真的不能改变代码，它可能会起作用。
回答by Azder
Simply if you use a framework that supports aspects, it would be easy to make an aspect that checks for the exception in the appropriate table. If not, then you could write something similar to this code:
简单地，如果您使用支持方面的框架，则可以很容易地创建一个方面来检查相应表中的异常。如果没有，那么您可以编写类似于此代码的内容：
ResultSet exRs = null;
try {
connection.setAutoCommit(false);
Statement statement = connection.createStatement();
statement.execute(normalSql);
exRs = statement.executeQuery(exceptionSql);
exRs.next();
} catch (SQLException e) {
e.printStackTrace();
connection.rollback();
}
if (null != exRs.getString(exceptionColumn))
connection.commit();
else
connection.rollback();
Sorry I couldn't be more specific.
对不起，我不能更具体。
