Discussions
Categories
- 197K All Categories
- 2.5K Data
- 546 Big Data Appliance
- 1.9K Data Science
- 450.8K Databases
- 221.9K General Database Discussions
- 3.8K Java and JavaScript in the Database
- 31 Multilingual Engine
- 552 MySQL Community Space
- 479 NoSQL Database
- 7.9K Oracle Database Express Edition (XE)
- 3.1K ORDS, SODA & JSON in the Database
- 556 SQLcl
- 4K SQL Developer Data Modeler
- 187.2K SQL & PL/SQL
- 21.4K SQL Developer
- 296.4K Development
- 17 Developer Projects
- 139 Programming Languages
- 293.1K Development Tools
- 111 DevOps
- 3.1K QA/Testing
- 646.1K Java
- 28 Java Learning Subscription
- 37K Database Connectivity
- 161 Java Community Process
- 105 Java 25
- 22.1K Java APIs
- 138.2K Java Development Tools
- 165.3K Java EE (Java Enterprise Edition)
- 19 Java Essentials
- 162 Java 8 Questions
- 86K Java Programming
- 81 Java Puzzle Ball
- 65.1K New To Java
- 1.7K Training / Learning / Certification
- 13.8K Java HotSpot Virtual Machine
- 94.3K Java SE
- 13.8K Java Security
- 205 Java User Groups
- 24 JavaScript - Nashorn
- Programs
- 475 LiveLabs
- 39 Workshops
- 10.2K Software
- 6.7K Berkeley DB Family
- 3.5K JHeadstart
- 5.7K Other Languages
- 2.3K Chinese
- 175 Deutsche Oracle Community
- 1.1K Español
- 1.9K Japanese
- 233 Portuguese
ORACLE/PLSQL: ORA-04091
Answers
-
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_;
/
-
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]....
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');
-
Thank you for your advise. The error is fixed with your suggestion
Regards,
Gunce