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
