SQL & PL/SQL

Announcement

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!

ORACLE/PLSQL: ORA-04091

gkayaDec 28 2015 — edited Dec 28 2015

Hi all,

I have a package that include a procedure. I'm using this procedure for inserting data to ps_acu_sf_tahs table if provide some conditions. But when I try to run as bellow:

begin

tahs_aktarim_.tahs_aktarim('45397','T1' );

end;

I m getting error: ORA-04091

I checked out all triggers that if effect ps_acu_sf_tahs table but there is no trigger which effect that table.

I just curious about what is the problem if there is no trigger for that table.

Is there anyone who faced similar weird problem?

Regards,

Gunce

This post has been answered by Paulzip on Dec 28 2015

Jump to Answer

Karthick2003

Can you post the entire error message? It should have the table name trigger name line number etc.

gkaya

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

Karthick2003

You have it all there. The trigger name the line number. What else do you want?

gkaya

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

Raj Nath

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.

Karthick2003

> 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.

gkaya

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?

Regards,

Gunce

Raj Nath

It is happening when you are invoking your procedure, then update must be getting triggered by your own program. Cross check your program please.

gkaya

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?

Raj Nath

how your procedure is affecting PS_ACU_SF_TAHS table? Are you updating? directly? If not then post your procedure code here.

gkaya

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_

FUNCTION taksit_bul (f_national_id CHAR)

RETURN CHAR

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

FROM ps_tahs_kriter)

GROUP BY emplid,

acad_career,

stdnt_car_nbr,

acad_prog,

acu_debit_term,

acu_debit_type,

acu_payment_term)

IS NULL

THEN

'01'

ELSE

( 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

FROM ps_tahs_kriter)

GROUP BY emplid,

acad_career,

stdnt_car_nbr,

acad_prog,

acu_debit_term,

acu_debit_type,

acu_payment_term)

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;

BEGIN

/*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,

acu_debit_type,

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

WHERE national_id = p_national_id;

IF v_banka_odeme_sekli = 'P1' OR v_banka_odeme_sekli = 'T1'

THEN

/*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,

v_acu_debit_type,

v_acu_payment_term,

taksit_bul (p_national_id),

v_acu_payment_typ,

a.amount,

v_currency_cd,

v_acu_kur,

a.amount * v_acu_kur,

'TUR',

v_bank_cd,

v_branch_ec_cd,

v_account_num,

'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'

THEN

SELECT amount / 2, updated, acu_flag3

INTO v_tahsilat_tutari, v_tahs_tarih, v_banka_odeme_sekli

FROM ps_acu_tahs

WHERE national_id = p_national_id AND acu_flag3 = p_flag;

/* insert fall debit amount*/

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,

v_acu_debit_type,

'G',

'01',

v_acu_payment_typ,

a.amount,

v_currency_cd,

v_acu_kur,

a.amount * v_acu_kur,

'TUR',

v_bank_cd,

v_branch_ec_cd,

v_account_num,

'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 prog_status IN ('AC', 'LA');

end tahs_aktarim_;

Raj Nath

what about your trigger code? post that also here.

Paulzip

Answer

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]....

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,

v_acu_debit_type,

v_acu_payment_term,

v_taksit_bul, -- NEW CHANGE HERE!!!

v_acu_payment_typ,

a.amount,

v_currency_cd,

v_acu_kur,

a.amount * v_acu_kur,

'TUR',

v_bank_cd,

v_branch_ec_cd,

v_account_num,

'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');

Marked as Answer by gkaya · Sep 27 2020

1 person found this helpful

gkaya

Thank you for your advise. The error is fixed with your suggestion

Regards,

Gunce

1 - 14

Locked Post

New comments cannot be posted to this locked post.

Locked on Jan 25 2016

Added on Dec 28 2015

#plsql, #trigger

14 comments

2,911 views

SQL & PL/SQL

ORACLE/PLSQL: ORA-04091

Comments

Post Details