Oracle Form FRM-40735：ON-ERROR 触发器引发了未处理的异常 ORA-06502
声明：本页面是StackOverFlow热门问题的中英对照翻译，遵循CC BY-SA 4.0协议，如果您需要使用它，必须同样遵循CC BY-SA许可，注明原文地址和作者信息，同时你必须将它归于原作者（不是我）：StackOverFlow
原文地址: http://stackoverflow.com/questions/9982932/
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 Form FRM-40735: ON-ERROR trigger raised unhandled exception ORA-06502
提问by user1308891
I have a table trigger like below:
我有一个如下所示的表触发器：
CREATE OR REPLACE TRIGGER PAT_BUR_DOB_TRG
BEFORE UPDATE OF DOB
ON PAT
REFERENCING OLD AS OLD NEW AS NEW
FOR EACH ROW
-- PL/SQL Block
begin
tgln_sys_error_pkg.compare_dates(trunc(add_months(:new.dob, -12)),
trunc(tgln_sys_error_pkg.GET_LIST_DATE(:old.pat_id)),
tgln_sys_errnums_pkg.en_retr_waitlist_date);
end;
--------------------------------------
I have a package which is called by the trigger above, the code for the package is like below:
我有一个由上面的触发器调用的包，包的代码如下：
CREATE OR REPLACE PACKAGE TGLN_SYS_ERROR_PKG AS
/* To compare two dates against each other. */
PROCEDURE COMPARE_DATES
(P_DATE_LOW date
,P_DATE_HIGH date
,P_ERROR_CODE number
);
FUNCTION GET_LIST_DATE
(P_PAT_ID number)
RETURN DATE;
END TGLN_SYS_ERROR_PKG;
--------------------------------------
The package body is like below:
包体如下所示：
CREATE OR REPLACE PACKAGE BODY TGLN_SYS_ERROR_PKG AS
FUNCTION GET_LIST_DATE(P_PAT_ID number) RETURN DATE IS
v_ret_date date;
begin
--select to_date('01-JAN-1980') into p_Date from dual;
select max(pwl.eff_date)
into v_ret_date
from pat, pat_register pr, pat_register_org_det prod, pat_wait_list pwl
where pat.pat_id = pr.pat_id
and pr.patr_id = prod.patr_id
and prod.prod_id = pwl.prod_id
and pat.pat_id = P_PAT_ID
and rownum < 2
AND pwl.exp_date is null;
return nvl(v_ret_date, to_date(null));
exception
when no_data_found then
return to_date(null);
end GET_LIST_DATE;
PROCEDURE COMPARE_DATES
(P_DATE_LOW date
,P_DATE_HIGH date
,P_ERROR_CODE number
)
IS
begin
if nvl(p_date_low,sysdate-10000)>nvl(p_date_high,sysdate+10000) then
raise_application_error(p_error_code,null);
end if;
end compare_dates;
end TGLN_SYS_ERROR_PKG;
--------------------------------------
CREATE OR REPLACE PACKAGE TGLN_SYS_ERRNUMS_PKG IS
en_retr_waitlist_date CONSTANT INTEGER := -20088; --Patient waitlist effective dates must not be less than or equal to patient's date of birth minus one year ( DOB - 1 year).
END TGLN_SYS_ERRNUMS_PKG;
--------------------------------------
Each time when Oracle Forms update DOB data, I get error like below:
每次 Oracle Forms 更新 DOB 数据时，我都会收到如下错误：
Oracle Form FRM-40735: ON-ERROR trigger raised unhandled exception ORA-06502
Oracle Form FRM-40735：ON-ERROR 触发器引发了未处理的异常 ORA-06502
But, when I hard code like below:
但是，当我硬编码如下：
select to_date('01-JAN-1980') into p_Date from dual;
to instead of this paragraph code like below, form works fine.
代替下面的这段代码，表单工作正常。
select max(pwl.eff_date)
into v_ret_date
from pat, pat_register pr, pat_register_org_det prod, pat_wait_list pwl
where pat.pat_id = pr.pat_id
and pr.patr_id = prod.patr_id
and prod.prod_id = pwl.prod_id
and pat.pat_id = P_PAT_ID
and rownum < 2
AND pwl.exp_date is null;
I did replace p_pat_idto a real value, it pops up a trigger error
我确实替换p_pat_id了一个真实的值，它弹出一个触发错误
ORA-04091: table TGLN.PAT is mutating, trigger/function may not see it
ORA-06512: at "TGLN.TGLN_SYS_ERROR_PKG",
line 130 ORA-06512: at "TGLN.PAT_BUR_DOB_TRG",
line 26 ORA-04088: error during execution of trigger 'TGLN.PAT_BUR_DOB_TRG'
View program sources of error stack?"
So, how to fix the bug? I can not hard code the date values
那么，如何修复这个bug呢？我不能硬编码日期值
采纳答案by Gaurav Soni
Error:
错误：
ORA-04091: table name is mutating, trigger/function may not see it/
ORA-04091: 表名正在变化，触发器/函数可能看不到它/
Your Error
你的错误
trigger error:"ORA-04091: table TGLN.PAT is mutating
trigger error:"ORA-04091: table TGLN.PAT is mutating
Cause:
原因：
A statement executed a trigger or custom PL/SQL function. That trigger/function tried to modify or query a table that is currently being modified by the statement that fired the trigger/function.
语句执行触发器或自定义 PL/SQL 函数。该触发器/函数试图修改或查询当前正在被触发触发器/函数的语句修改的表。
Your Cause
你的事业
--you're not supposed to query a table that is currently modified
--In you're case its `PAT` table being updated and querying at same time
select max(pwl.eff_date)
into v_ret_date
from pat, --here you are querying your PAT table,while updating the same table
pat_register pr, pat_register_org_det prod, pat_wait_list pwl
where pat.pat_id = pr.pat_id
and pr.patr_id = prod.patr_id
and prod.prod_id = pwl.prod_id
and pat.pat_id = P_PAT_ID
and rownum < 2
AND pwl.exp_date is null;
Action:
行动：
The options to resolve this Oracle error are:
Re-write the trigger/function so that it does not try to modify/querythe table PATin question.
解决此 Oracle 错误的选项是： 重新编写触发器/函数，使其不会尝试访问modify/query有PAT问题的表。
Reference
参考
