Forum Stats

  • 3,838,227 Users
  • 2,262,342 Discussions
  • 7,900,547 Comments

Discussions

ORACLE/PLSQL: ORA-04091

gkaya
gkaya Member Posts: 49
edited Dec 28, 2015 8:42AM in SQL & PL/SQL

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

Tagged:
JuanM

Best Answer

  • Paulzip
    Paulzip Member Posts: 8,720 Blue Diamond
    edited Dec 28, 2015 8:18AM 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');

    JuanM
«1

Answers

  • Karthick2003
    Karthick2003 Member Posts: 13,711 Bronze Badge
    edited Dec 28, 2015 3:59AM

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

  • gkaya
    gkaya Member Posts: 49
    edited Dec 28, 2015 4:14AM

    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
    Karthick2003 Member Posts: 13,711 Bronze Badge
    edited Dec 28, 2015 5:00AM

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

  • gkaya
    gkaya Member Posts: 49
    edited Dec 28, 2015 5:06AM

    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
    Raj Nath Member Posts: 394
    edited Dec 28, 2015 5:17AM

    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
    Karthick2003 Member Posts: 13,711 Bronze Badge
    edited Dec 28, 2015 6:07AM

    > 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
    gkaya Member Posts: 49
    edited Dec 28, 2015 6:13AM

    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
    Raj Nath Member Posts: 394
    edited Dec 28, 2015 6:30AM

    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
    gkaya Member Posts: 49
    edited Dec 28, 2015 6:39AM

    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
    Raj Nath Member Posts: 394
    edited Dec 28, 2015 6:46AM

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

This discussion has been closed.