oracle RAISE_APPLICATION_ERROR 问题
声明：本页面是StackOverFlow热门问题的中英对照翻译，遵循CC BY-SA 4.0协议，如果您需要使用它，必须同样遵循CC BY-SA许可，注明原文地址和作者信息，同时你必须将它归于原作者（不是我）：StackOverFlow
原文地址: http://stackoverflow.com/questions/6415340/
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
RAISE_APPLICATION_ERROR issue
提问by haunted85
I have developed a trigger that checks the validity of a date. It works fine because it prevents me from storing an invalid date, but I also get a weird error message and I can't figure out why. My code is the following:
我开发了一个触发器来检查日期的有效性。它工作正常，因为它阻止我存储无效日期，但我也收到一条奇怪的错误消息，我不知道为什么。我的代码如下：
CREATE OR REPLACE TRIGGER "CHECKDATEVALIDITY"
BEFORE INSERT OR UPDATE
ON Event
FOR EACH ROW
BEGIN
IF :NEW.day < 1 OR :NEW.month < 1 OR :NEW.month > 12
THEN
RAISE_APPLICATION_ERROR(-20101, 'Wrong date');
END IF;
IF :NEW.month = 4 OR :NEW.month = 6 OR :NEW.month = 9 OR :NEW.month = 11
THEN
IF :NEW.day > 30
THEN
RAISE_APPLICATION_ERROR(-20101, 'Wrong date');
END IF;
ELSIF :NEW.month = 2
THEN
IF (mod(:NEW.year, 4) = 0)
THEN
IF :NEW.day > 29
THEN
RAISE_APPLICATION_ERROR(-20101, 'Wrong date');
END IF;
ELSIF :NEW.day > 28
THEN
RAISE_APPLICATION_ERROR(-20101, 'Wrong date');
END IF;
ELSE
IF :NEW.day > 31
THEN
RAISE_APPLICATION_ERROR(-20101, 'Wrong date');
END IF;
END IF;
END checkDateValidity;
The error I get is:
我得到的错误是：
error ORA-20101: Wrong date ORA-06512: on "USER587.CHECKDATEVALIDITY", line 28 ORA-04088: error while executing trigger 'USER578.CHECKDATEVALIDITY'.
错误 ORA-20101：错误的日期 ORA-06512：在“USER587.CHECKDATEVALIDITY”，第 28 行 ORA-04088：执行触发器 'USER578.CHECKDATEVALIDITY' 时出错。
Also I have noticed that I get the error from the line next to the RAISE_APPLICATION_ERROR invoked. What does issue the error?
另外我注意到我从调用的 RAISE_APPLICATION_ERROR 旁边的行中得到错误。什么问题会导致错误？
回答by Justin Cave
What do you consider the "wierd error message"? It looks like a perfectly reasonable stack trace to me. At the bottom of the stack, you got an error executing a trigger. The next line tells you that the error happened at line 28. The top of the stack is your custom error message and number. That all seems quite normal to me (though you appear to have cut off some of the error text associated with the ORA-06512 error)
您如何看待“奇怪的错误消息”？对我来说，它看起来像是一个完全合理的堆栈跟踪。在堆栈的底部，您在执行触发器时遇到错误。下一行告诉您错误发生在第 28 行。堆栈的顶部是您自定义的错误消息和编号。这一切对我来说似乎很正常（尽管您似乎已经切断了与 ORA-06512 错误相关的一些错误文本）
ORA-20101: Wrong date
ORA-06512: on "USER587.CHECKDATEVALIDITY", line 28
ORA-04088: error while executing trigger 'USER578.CHECKDATEVALIDITY'.
If you're trying to match up the line number, take a look at DBA_SOURCE. For example, this will show you what is on lines 23-32 of your trigger (the offending line +/- 5 lines).
如果您想匹配行号，请查看DBA_SOURCE. 例如，这将显示触发器的第 23-32 行（违规行 +/- 5 行）上的内容。
SELECT line, text
FROM dba_source
WHERE owner = 'USER578'
AND name = 'CHECKDATEVALIDITY'
AND line BETWEEN 23 and 32;
Of course, I assume this is a classroom exercise and not something you're doing in the real world. In the real world, you'd store in a DATE column and let Oracle take care of ensuring that a valid date was entered.
当然，我认为这是课堂练习，而不是您在现实世界中所做的事情。在现实世界中，您将存储在 DATE 列中，并让 Oracle 负责确保输入的日期有效。
回答by mukraish
This means you are (by your own rules) inserting an invalid day.
这意味着您（根据您自己的规则）插入了无效的一天。
Your trigger is raising ora-20101 if the day is greater than 31, and it does just that.
如果一天大于 31，您的触发器会引发 ora-20101，它就是这样做的。
