Oracle：什么情况下使用RAISE_APPLICATION_ERROR？
声明：本页面是StackOverFlow热门问题的中英对照翻译，遵循CC BY-SA 4.0协议，如果您需要使用它，必须同样遵循CC BY-SA许可，注明原文地址和作者信息，同时你必须将它归于原作者（不是我）：StackOverFlow
原文地址: http://stackoverflow.com/questions/1760596/
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
Oracle: what is the situation to use RAISE_APPLICATION_ERROR?
提问by Ricky
We can use RAISEto fire an exception. What particular situations do we need to use RAISE_APPLICATION_ERROR?
我们可以使用RAISE来触发异常。我们需要在哪些特定情况下使用RAISE_APPLICATION_ERROR？
Thanks.
谢谢。
回答by APC
There are two uses for RAISE_APPLICATION_ERROR. The first is to replace generic Oracle exception messages with our own, more meaningful messages. The second is to create exception conditions of our own, when Oracle would not throw them.
RAISE_APPLICATION_ERROR 有两种用途。第一个是用我们自己的更有意义的消息替换通用的 Oracle 异常消息。第二个是创建我们自己的异常条件，当 Oracle 不会抛出它们时。
The following procedure illustrates both usages. It enforces a business rule that new employees cannot be hired in the future. It also overrides two Oracle exceptions. One is DUP_VAL_ON_INDEX, which is thrown by a unique key on EMP(ENAME). The other is a a user-defined exception thrown when the foreign key between EMP(MGR)and EMP(EMPNO)is violated (because a manager must be an existing employee).
以下过程说明了这两种用法。它强制执行一项业务规则，即将来不能雇用新员工。它还覆盖了两个 Oracle 异常。一种是 DUP_VAL_ON_INDEX，它由 上的唯一键抛出EMP(ENAME)。另一个是当EMP(MGR)和之间的外键EMP(EMPNO)被违反时抛出的用户定义异常（因为经理必须是现有员工）。
create or replace procedure new_emp
( p_name in emp.ename%type
, p_sal in emp.sal%type
, p_job in emp.job%type
, p_dept in emp.deptno%type
, p_mgr in emp.mgr%type
, p_hired in emp.hiredate%type := sysdate )
is
invalid_manager exception;
PRAGMA EXCEPTION_INIT(invalid_manager, -2291);
dummy varchar2(1);
begin
-- check hiredate is valid
if trunc(p_hired) > trunc(sysdate)
then
raise_application_error
(-20000
, 'NEW_EMP::hiredate cannot be in the future');
end if;
insert into emp
( ename
, sal
, job
, deptno
, mgr
, hiredate )
values
( p_name
, p_sal
, p_job
, p_dept
, p_mgr
, trunc(p_hired) );
exception
when dup_val_on_index then
raise_application_error
(-20001
, 'NEW_EMP::employee called '||p_name||' already exists'
, true);
when invalid_manager then
raise_application_error
(-20002
, 'NEW_EMP::'||p_mgr ||' is not a valid manager');
end;
/
How it looks:
它的外观：
SQL> exec new_emp ('DUGGAN', 2500, 'SALES', 10, 7782, sysdate+1)
BEGIN new_emp ('DUGGAN', 2500, 'SALES', 10, 7782, sysdate+1); END;
*
ERROR at line 1:
ORA-20000: NEW_EMP::hiredate cannot be in the future
ORA-06512: at "APC.NEW_EMP", line 16
ORA-06512: at line 1
SQL>
SQL> exec new_emp ('DUGGAN', 2500, 'SALES', 10, 8888, sysdate)
BEGIN new_emp ('DUGGAN', 2500, 'SALES', 10, 8888, sysdate); END;
*
ERROR at line 1:
ORA-20002: NEW_EMP::8888 is not a valid manager
ORA-06512: at "APC.NEW_EMP", line 42
ORA-06512: at line 1
SQL>
SQL> exec new_emp ('DUGGAN', 2500, 'SALES', 10, 7782, sysdate)
PL/SQL procedure successfully completed.
SQL>
SQL> exec new_emp ('DUGGAN', 2500, 'SALES', 10, 7782, sysdate)
BEGIN new_emp ('DUGGAN', 2500, 'SALES', 10, 7782, sysdate); END;
*
ERROR at line 1:
ORA-20001: NEW_EMP::employee called DUGGAN already exists
ORA-06512: at "APC.NEW_EMP", line 37
ORA-00001: unique constraint (APC.EMP_UK) violated
ORA-06512: at line 1
Note the different output from the two calls to RAISE_APPLICATION_ERROR in the EXCEPTIONS block. Setting the optional third argument to TRUE means RAISE_APPLICATION_ERROR includes the triggering exception in the stack, which can be useful for diagnosis.
注意 EXCEPTIONS 块中两次调用 RAISE_APPLICATION_ERROR 的不同输出。将可选的第三个参数设置为 TRUE 意味着 RAISE_APPLICATION_ERROR 包括堆栈中的触发异常，这对诊断很有用。
There is more useful information in the PL/SQL User's Guide.
PL/SQL 用户指南中有更多有用的信息。
回答by RC.
You use RAISE_APPLICATION_ERRORin order to create an Oracle style exception/error that is specific to your code/needs. Good use of these help to produce code that is clearer, more maintainable, and easier to debug.
您使用RAISE_APPLICATION_ERROR为了创建特定于您的代码/需求的 Oracle 样式异常/错误。充分利用这些有助于生成更清晰、更易于维护且更易于调试的代码。
For example, if I have an application calling a stored procedure that adds a user and that user already exists, you'll usually get back an error like:
例如，如果我有一个应用程序调用一个添加用户的存储过程，并且该用户已经存在，您通常会得到如下错误：
ORA-00001: unique constraint (USERS.PK_USER_KEY) violated
Obviously this error and associated message are not unique to the task you were trying to do. Creating your own Oracle application errors allow you to be clearer on the intent of the action and the cause of the issue.
显然，此错误和相关消息并非您尝试执行的任务所独有。创建您自己的 Oracle 应用程序错误可以让您更清楚地了解操作的意图和问题的原因。
raise_application_error(-20101, 'User ' || in_user || ' already exists!');
Now your application code can write an exception handler in order to process this specific error condition. Think of it as a way to make Oracle communicate error conditions that your application expects in a "language" (for lack of a better term) that you have defined and is more meaningful to your application's problem domain.
现在您的应用程序代码可以编写一个异常处理程序来处理这个特定的错误情况。将其视为一种使 Oracle 以您定义的“语言”（因为缺少更好的术语）传达您的应用程序期望的错误条件的方法，并且对您的应用程序的问题域更有意义。
Note that user defined errors must be in the range between -20000 and -20999.
请注意，用户定义的错误必须在 -20000 和 -20999 之间的范围内。
The following linkprovides lots of good information on this topic and Oracle exceptions in general.
以下链接提供了许多关于此主题和一般 Oracle 异常的好信息。
回答by dcp
Just to elaborate a bit more on Henry's answer, you can also use specific error codes, from raise_application_error and handle them accordingly on the client side. For example:
只是为了详细说明亨利的回答，您还可以使用来自 raise_application_error 的特定错误代码，并在客户端相应地处理它们。例如：
Suppose you had a PL/SQL procedure like this to check for the existence of a location record:
假设您有一个像这样的 PL/SQL 过程来检查位置记录的存在：
PROCEDURE chk_location_exists
(
p_location_id IN location.gie_location_id%TYPE
)
AS
l_cnt INTEGER := 0;
BEGIN
SELECT COUNT(*)
INTO l_cnt
FROM location
WHERE gie_location_id = p_location_id;
IF l_cnt = 0
THEN
raise_application_error(
gc_entity_not_found,
'The associated location record could not be found.');
END IF;
END;
The raise_application_error allows you to raise a specific error code. In your package header, you can define:
gc_entity_not_found INTEGER := -20001;
raise_application_error 允许您引发特定的错误代码。在您的包头中，您可以定义：
gc_entity_not_found INTEGER := -20001;
If you need other error codes for other types of errors, you can define other error codes using -20002, -20003, etc.
如果您需要其他类型错误的其他错误代码，您可以使用 -20002、-20003 等定义其他错误代码。
Then on the client side, you can do something like this (this example is for C#):
然后在客户端，您可以执行以下操作（此示例适用于 C#）：
/// <summary>
/// <para>Represents Oracle error number when entity is not found in database.</para>
/// </summary>
private const int OraEntityNotFoundInDB = 20001;
And you can execute your code in a try/catch
你可以在 try/catch 中执行你的代码
try
{
// call the chk_location_exists SP
}
catch (Exception e)
{
if ((e is OracleException) && (((OracleException)e).Number == OraEntityNotFoundInDB))
{
// create an EntityNotFoundException with message indicating that entity was not found in
// database; use the message of the OracleException, which will indicate the table corresponding
// to the entity which wasn't found and also the exact line in the PL/SQL code where the application
// error was raised
return new EntityNotFoundException(
"A required entity was not found in the database: " + e.Message);
}
}
回答by Henry Gao
if your application accepts errors raise from Oracle, then you can use it. we have an application, each time when an error happens, we call raise_application_error, the application will popup a red box to show the error message we provide through this method.
如果您的应用程序接受来自 Oracle 的错误，那么您可以使用它。我们有一个应用程序，每次发生错误时，我们调用raise_application_error，应用程序会弹出一个红色框显示我们通过该方法提供的错误信息。
When using dotnet code, I just use "raise", dotnet exception mechanisim will automatically capture the error passed by Oracle ODP and shown inside my catch exception code.
使用 dotnet 代码时，我只使用“raise”，dotnet 异常机制将自动捕获 Oracle ODP 传递的错误并显示在我的捕获异常代码中。
