Forum Stats

  • 3,876,221 Users
  • 2,267,082 Discussions
  • 7,912,474 Comments

Discussions

ORACLE/PLSQL: ORA-04091

2»

Answers

  • gkaya
    gkaya Member Posts: 49
    edited Dec 28, 2015 7:08AM

    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

                                             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)

       IS

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

    what about your trigger code? post that also here.

  • Paulzip
    Paulzip Member Posts: 8,812 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
  • gkaya
    gkaya Member Posts: 49
    edited Dec 28, 2015 8:42AM

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

    Regards,

    Gunce

This discussion has been closed.