Oracle Form FRM-40735: ON-ERROR trigger raised unhandled exception ORA-06502

Question

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 error

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

Answer 1

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

• ORA-04091 Error

• ORA-04091: table [blah] is mutating, trigger/function may not see it