For appeals, questions and feedback about Oracle Forums, please email oracle-forums-moderators_us@oracle.com. Technical questions should be asked in the appropriate category. Thank you!
With the introduction of java.time, why did you not flag java.util.Date and java.util.Calendar. These classes have been a bane to every Java developer and should never be used again with the introduction of Java 1.8.
Can you post the entire error message? It should have the table name trigger name line number etc.
ORA-04091: table SYSPROD.PS_ACU_SF_TAHS is mutating, trigger/function may not see it
ORA-06512: at "SYSPROD.TAHS_AKTARIM_", line 7
ORA-06512: at "SYSPROD.TAHS_AKTARIM_", line 64
ORA-06512: at line 2
You have it all there. The trigger name the line number. What else do you want?
I checked line number and at line 64 I try to insert data in ps_Acu_sf_tahs table. I looked at any trigger which is related ps_Acu_Sf_tahs table but there is not trigger as I want.
I select bellow table:
select * from user_procedures where object_type='TRIGGER'
select * from user_triggers
select * from dba_triggers
select * from all_triggers
but I could not faced any trigger related ps_acu_sF_tahs
Do you have any advise for that?
Regards,
Gunce
Your error message is clear, check trigger code and see it, if you have any problem then you can put trigger here and we can see and tell you where it is going wrong. But i am sure if you check your code, you will be able to debug it.
> I checked line number and at line 64 I try to insert data in ps_Acu_sf_tahs table
Exactly that's the reason you are getting that error. You cant reference to the table on which a row level trigger is written. Instead use :new to set the value that you intend to insert.
The interesting is that there is not any trigger or function that change or effect ps_acu_Sf_tahs table. If there is an trigger or function probably I must not have found their name in db.
How can I know or see that's happened due to which trigger or function?
Do you have any idea or peace of code for see it to debug?
It is happening when you are invoking your procedure, then update must be getting triggered by your own program. Cross check your program please.
I know that this error happened when I exec procedure. and I also know which line is getting problem but I do not know how can I find trigger or function which is source of that error.
What do you think about that?
how your procedure is affecting PS_ACU_SF_TAHS table? Are you updating? directly? If not then post your procedure code here.
I have a package that include a procedure as I try to exec as bellow:
begin
tahs_aktarim_.tahs_aktarim('45397','T1' );
end;
I try to insert data to ps_Acu_sf_tahs table and I m getting error.
/* Formatted on 28.12.2015 14:04:34 (QP5 v5.149.1003.31008) */
CREATE OR REPLACE PACKAGE BODY tahs_aktarim_
AS
FUNCTION taksit_bul (f_national_id CHAR)
RETURN CHAR
IS
v_yeni_debit_taksit CHAR;
BEGIN
SELECT CASE
WHEN ( SELECT LPAD (NVL (COUNT (*) + 1, 0), 2, 0)
FROM ps_acu_sf_tahs k
WHERE (emplid, acad_career, stdnt_car_nbr, acad_prog) IN
(SELECT emplid,
acad_career,
stdnt_car_nbr,
acad_prog
FROM ps_acu_stu_info a, ps_acu_tahs b
WHERE a.national_id = f_national_id
AND a.national_id = b.national_id
AND k.emplid = a.emplid)
AND acu_debit_term =
(SELECT acu_debit_term
FROM ps_tahs_kriter)
AND acu_payment_term =
(SELECT acu_payment_term
GROUP BY emplid,
acad_prog,
acu_debit_term,
acu_debit_type,
acu_payment_term)
IS NULL
THEN
'01'
ELSE
( SELECT LPAD (NVL (COUNT (*) + 1, 0), 2, 0)
(SELECT acu_debit_term FROM ps_tahs_kriter)
END
AS debit_taksit
INTO v_yeni_debit_taksit
FROM DUAL;
END;
PROCEDURE tahs_aktarim (p_national_id CHAR, p_flag CHAR)
nid NUMBER;
v_emplid NUMBER;
v_acu_debit_term VARCHAR2 (4 CHAR);
v_acu_payment_term VARCHAR2 (1 CHAR);
v_currency_cd VARCHAR2 (10 CHAR);
v_acu_kur NUMBER;
v_acu_payment_typ VARCHAR2 (5 CHAR);
v_acu_flag VARCHAR2 (5 CHAR);
v_comments VARCHAR2 (300 CHAR);
v_bank_cd VARCHAR2 (2);
v_branch_ec_cd VARCHAR2 (50);
v_account_num VARCHAR2 (50 CHAR);
v_acu_debit_taksit VARCHAR2 (3 CHAR);
v_acu_debit_type VARCHAR2 (5 CHAR);
v_oprid VARCHAR2 (50 CHAR);
v_total_receivable NUMBER;
v_tahsilat_tutari NUMBER;
v_tahs_tarih NUMBER;
v_banka_odeme_sekli VARCHAR (2 CHAR); --P0, P1, T1, T2...
v_yeni_debit_taksit VARCHAR (20 CHAR);
v_amount NUMBER;
v_updated DATE;
/*criterion of receipt*/
SELECT acu_debit_term,
acu_payment_term,
DECODE (currency_cd, 'TL', 'TRY', currency_cd),
acu_kur,
acu_payment_typ,
acu_flag,
comments,
bank_cd,
branch_ec_cd,
account_num,
acu_debit_taksit,
oprid
INTO v_acu_debit_term,
v_acu_payment_term,
v_currency_cd,
v_acu_kur,
v_acu_payment_typ,
v_acu_flag,
v_comments,
v_bank_cd,
v_branch_ec_cd,
v_account_num,
v_acu_debit_taksit,
v_acu_debit_type,
v_oprid
FROM ps_tahs_kriter;
/*count of total receipt based on payment type*/
--select count(*) into v_total_receivable from ps_acu_tahs where national_id=p_national_id;
/*receipt information of students*/
SELECT amount, updated, acu_flag3
INTO v_tahsilat_tutari, v_tahs_tarih, v_banka_odeme_sekli
FROM ps_acu_tahs
WHERE national_id = p_national_id;
/*students' emplid*/
SELECT emplid
INTO v_emplid
FROM ps_acu_stu_info
IF v_banka_odeme_sekli = 'P1' OR v_banka_odeme_sekli = 'T1'
/*insert receipt information to ps_Acu_sf_tahs table*/ --> SOURCE OF ERROR!!
INSERT INTO ps_acu_sf_tahs
SELECT 'ACU',
b.emplid,
b.acad_career,
b.stdnt_car_nbr,
b.acad_prog,
ps_acu_tahs_sequence.NEXTVAL,
TO_DATE (a.updated, 'RRRRMMDD'),
v_acu_debit_term,
taksit_bul (p_national_id),
a.amount,
a.amount * v_acu_kur,
'TUR',
'N',
v_oprid,
SYSDATE,
v_comments
FROM ps_acu_tahs a, ps_acu_stu_info b
WHERE a.national_id = b.national_id
AND a.national_id = p_national_id
AND acu_flag3 = p_flag
AND prog_status IN ('AC', 'LA');
IF v_banka_odeme_sekli = 'P0'
SELECT amount / 2, updated, acu_flag3
WHERE national_id = p_national_id AND acu_flag3 = p_flag;
/* insert fall debit amount*/
PS_ACU_TAHS_SEQUENCE.NEXTVAL,
'G',
'01',
FROM ps_acu_TAHS a, ps_acu_stu_info b
end tahs_aktarim_;
/
what about your trigger code? post that also here.
You are using a function in an insert statement which is reading data from the table it is trying to change.
Relational DBs have to be atomic (anything a DML statement changes is assumed to happen at the same time) and as such things that act on data in DML have to be deterministic - i.e. give the same result even if execution plan and concurrency differs. Your situation isn't deterministic, so Oracle throws an error.
The easiest solution is to select the result of taksit_bul (p_national_id) into a variable, and use the variable in the insert statement. That should cure the mutating table.
v_taksit_bul := taksit_bul (p_national_id);
[snip]....
v_taksit_bul, -- NEW CHANGE HERE!!!
Thank you for your advise. The error is fixed with your suggestion