Oracle Form FRM-40735: ON-ERROR trigger raised unhandled exception ORA-06502
Error:
ORA-04091: table name is mutating, trigger/function may not see it/
Your Error
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.
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/query
the table PAT
in question.
Reference
user1308891
Updated on April 03, 2020Comments
-
user1308891 about 4 years
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 Form FRM-40735: ON-ERROR trigger raised unhandled exception 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_id
to a real value, it pops up a trigger errorORA-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