Home » SQL & PL/SQL » SQL & PL/SQL » ORA-04091,ORA-06512
| ORA-04091,ORA-06512 [message #9301] |
Sun, 02 November 2003 18:12  |
wayne
Messages: 27
Registered: March 2001
|
Junior Member |
|
|
Dear friend,
I want to do a trigger which enable to prompt out error message when the certain products are out of stock.
In fact, the trigger should be simple and easy to do, but I still receive error message called "ORA-04091: table SCOTT.PRODUCT is mutating, trigger/function may not see it"
Can you check for me, which parts I did wrongly. Thank you very much.
create table vendors (
vendorID varchar2(6) constraint pk_vendorID primary key,
vendorName varchar2(15) not null,
contact number(12),
address varchar2(20));
create table product (
productCode varchar2(6) constraint pk_productNo primary key,
productName varchar2(15) not null,
quantity number(3) default(0) constraint check_quantities check(quantity >=0),
reorder number(3) default(0) constraint check_reorder check(reorder >= 0),
vendorID varchar2(6) constraint fk_product_vendorID references Vendors(vendorID) );
create table purchaseOrder (
pOrderNo varchar2(6) constraint pk_purchaseOrder primary key,
productCode varchar2(6) constraint fk_purchaseOrder_proCode references product(productCode),
quantity number(3) default(0) constraint check_quantityOrder check(quantity >=0),
vendorID varchar2(6) constraint fk_purchaseOrder_vendorsID references Vendors(vendorID));
insert into vendors values ('V00001', 'Bear', 0340421934, '10A,Jln Bear');
insert into vendors values ('V00002', 'Fish', 0340421934, '10A,Jln Bear');
insert into product values ('P00001', 'Biscuit', 13, 12, 'V00001');
SQL> Create or replace trigger checkQuantity
2 After Insert or Update of quantity on product
3 For each row
4
5 Declare
6 quantityX number(3);
7
8 Begin
9 Select quantity into quantityX from product
10 where productCode = :NEW.productCode;
11
12 IF( :NEW.REORDER > quantityX ) THEN
13 DBMS_OUTPUT.ENABLE;
14 DBMS_OUTPUT.PUT_LINE('OUT OF ORDER');
15 END IF;
16 END;
17 /
Trigger created.
SQL> delete from product;
0 rows deleted.
SQL> insert into product values ('P00001', 'Biscuit', 10, 12, 'V00001');
insert into product values ('P00001', 'Biscuit', 10, 12, 'V00001')
*
ERROR at line 1:
ORA-04091: table SCOTT.PRODUCT is mutating, trigger/function may not see it
ORA-06512: at "SCOTT.CHECKQUANTITY", line 5
ORA-04088: error during execution of trigger 'SCOTT.CHECKQUANTITY'
SQL> insert into product values ('P00001', 'Biscuit', 13, 12, 'V00001');
insert into product values ('P00001', 'Biscuit', 13, 12, 'V00001')
*
ERROR at line 1:
ORA-04091: table SCOTT.PRODUCT is mutating, trigger/function may not see it
ORA-06512: at "SCOTT.CHECKQUANTITY", line 5
ORA-04088: error during execution of trigger 'SCOTT.CHECKQUANTITY'
Thank you for your help, thank you.
thankyou,
wayne
|
|
|
|
| Re: ORA-04091,ORA-06512 [message #9305 is a reply to message #9301] |
Sun, 02 November 2003 23:55  |
 |
Maaher
Messages: 7065
Registered: December 2001
|
Senior Member |
|
|
Well, this question pops up from time to time. You are encountering the famous 'mutating table' problem.
The source of the problem is that you are trying to read from a table while you are changing it. Oracle can no longer guarantee read consistency and therefor
raises the ORA 4091.
Here's an excerpt of a document I once created:
Oracle Mutating Table Problem and how to avoid it
What is the Mutating Table Problem exactly?
A mutating table is a table that is currently being modified by an update, delete, or insert statement. You will encounter the ORA-4091 error if you have a row trigger that reads or modifies the mutating table.
A constraining table is a table that a triggering statement might need to read either directly, for a SQL statement, or indirectly, for a declarative referential integrity constraint. A table is mutating or constraining only to the session that issued the statement in progress.
For example, if your trigger contains a select statement or an update statement referencing the table it is triggering off of you will receive the error. Another way this error can occur is if the trigger has statements to change the primary, foreign or unique key columns of the table the trigger is triggering off of.
If you must have triggers on tables that have referential constraints, the workaround is to enforce the referential integrity through triggers as well.
Assume that you have table EMP(empno, ename, sal, deptno):EMPNO ENAME SAL DEPTNO
----- ---------- -------- ------
00001 BECKER 1000 10
00002 JONES 1250 10
...
00503 WARNER 1025 95
This table has an AFTER ROW INSERT/UPDATE trigger that
verifies that a certain maximum of the total salaries
is not yet met:
...
...
Begin
Select sum(sal)
Into v_total
From emp;
...
....
Now, imagine that you perform the following update:
UPDATE EMP SET SAL = SAL*1.1;
Notice that the SQL statement is run for the first row of the table, and then an AFTER row trigger is fired. In turn, a statement in the AFTER row trigger body attempts to query the original table. However, because the EMP table is mutating( the content has already changed) , this query is not allowed by Oracle. If attempted, then a runtime error occurs, the effects of the trigger body and triggering statement are rolled back, and control is returned to the user or application.
How can I avoid a mutating table?
If you need to update a mutating or constraining table, then you could use a temporary table, a PL/SQL table, or a package variable to bypass these restrictions.
For example, in place of a single AFTER row trigger that updates the original table, resulting in a mutating table error, you may be able to use two triggers. The first is an AFTER row trigger that updates a temporary table, and the second an AFTER statement trigger that updates the original table with the values from the temporary table.
Example
Consider table
EMPLOYEES (employee_id number, department_id number, name varchar2 (20), wage number);
If one creates a before insert or update trigger which checks that the total of wages for a department doesn’t exceed 2 million Belgian Francs.
One could say:
CREATE TRIGGER BRUI_EMPLOYEES
BEFORE INSERT OR UPDATE ON Employees
FOR EACH ROW
DECLARE
CURSOR c_wages
IS SELECT count(wages) total_wages
WHERE department_id = :NEW.department_id
FROM EMPLOYEES;
v_total_wages NUMBER;
BEGIN
OPEN c_wages;
FETCH c_wages INTO v_total_wages;
CLOSE c_wages;
IF v_total_wages > 2000000 THEN
<DIR>
<DIR>
<DIR>
<DIR>
-- do exception handling
-- some exception package is being called
PCK$EXCEPTIONS.RAISE(‘Total wages exceeds budget’);
</DIR>
</DIR>
END IF;
</DIR>
</DIR>
END;
We issue the following command after creating the trigger:
SQL>UPDATE employees
SET wage = 14500
WHERE id = 6;
This command will fail. It will result in an ORA-4091 error, since we’re trying to read the table while we modify it.
Therefore, we need to adjust the processing a little bit:
<OL>
[*]Create a package specification (body is not necessary at this time, since we don’t do any actual processing in the package itself, and it doesn’t need to contain any logic).
CREATE OR REPLACE PACKAGE pck$mutations
IS
Gv_department_id NUMBER; -- global variable
END pck$mutations;
[*]Create a before insert/update trigger for each row to store the department id in the global variable.
CREATE TRIGGER BRUI_EMPLOYEES
BEFORE INSERT OR UPDATE ON Employees
FOR EACH ROW
BEGIN
Pck$mutations.Gv_department_id :=:NEW.department_id; -- store
--department id in global variable
END;
[*]
[*]Create an after insert/update statement trigger do the actual check. </OL>
CREATE TRIGGER ASUI_EMPLOYEES
AFTER INSERT OR UPDATE ON Employees
DECLARE
CURSOR c_wages
IS SELECT count(wages) total_wages
WHERE department_id = pck$mutations.department_id
FROM EMPLOYEES;
v_total_wages NUMBER;
BEGIN
OPEN c_wages;
FETCH c_wages INTO v_total_wages;
CLOSE c_wages;
IF v_total_wages > 2000000 THEN
<DIR>
<DIR>
<DIR>
<DIR>
-- do exception handling
-- some exception package is being called
PCK$EXCEPTIONS.RAISE(‘Total wages exceeds budget’);
</DIR>
</DIR>
END IF;
</DIR>
</DIR>
END;
To summarize: If one encounters a mutating table issue, he/she should do basically the following:
- Create a package header to contain any :NEW or :OLD values that are necessary to perform checks.
- Create a before each row trigger that fills these variables.
- Create an after statement trigger that performs the checks.
Useful remarks
<OL>
[*]An after statement trigger fires before any changes are saved to the database. If the trigger fails/raises an error, all changes of that transaction are rolled back.
[*]When processing so-called bulk inserts/updates, the system might need PL/SQL tables to contain the variables. This is due to the fact that a row level trigger fires for each row (i.e. possibly more times in a bulk operation) and a statement trigger fires only once per statement (hence the names). In the before row trigger one should insert a record in the PL/SQL table. In the after statement trigger, loop through the records of the PL/SQL table and do the necessary processing.
[*]Useful internet addresses:
Oracle Technology Network (OTN), the technical website of Oracle corporation. http://otn.oracle.com or http://technet.oracle.com
Oracle support: Metalink (requires support Id and password): http://metalink.oracle.com
[*]You could add the package body PCK$MUT, to write a sort of API around the variable and make it hidden for calling triggers. That way, no one can directly access the variable. You could write a SET_VAR procedure to fill the variable, a GET_VAR function to retrieve the value of the variable, a CLEAR_VAR procedure to empty the variable…. </OL>
If not clear you can always search the boards here for 'Mutating table' or http://asktom.oracle.com (Tom Kytes' website, a site to put in your favourites). I strongly recommend to look at AskTom. Usually he's very clear and uses straightforward examples.
MHE
|
|
|
|
|
|
|
|
|
|
| Re: ORA-04091,ORA-06512 [message #477820 is a reply to message #477794] |
Mon, 04 October 2010 10:40 |
 |
Barbara Boehmer
Messages: 9088
Registered: November 2002
Location: California, USA
|
Senior Member |
|
|
In addition to the method that uses a package and triggers, there is another method that uses a constraint on a materialized view. The following demonstration uses emp2, adds a column, a sequence, and a before insert row trigger to populate the column with the sequence. If one of your columns is already a sequence, then you can use that instead. It then creates a materialized view log, a materialized view with refresh fast on commit that selects from a self-join on the emp2 table a count of those rows that would be in violation. It then creates a check constraint on the materialized view that the column that counts the violations = 0. I have then demonstrated the scenarios that you supplied, showing the desired behavior.
-- original table:
SCOTT@orcl_11gR2> CREATE Table Emp2
2 (Col1 VARCHAR2(4) PRIMARY KEY,
3 Col2 VARCHAR2(4),
4 Col3 VARCHAR2(4))
5 /
Table created.
-- add column, sequence, and trigger:
SCOTT@orcl_11gR2> ALTER TABLE emp2 ADD (col4 NUMBER)
2 /
Table altered.
SCOTT@orcl_11gR2> CREATE SEQUENCE emp2_seq
2 /
Sequence created.
SCOTT@orcl_11gR2> CREATE OR REPLACE TRIGGER emp2_bir
2 BEFORE INSERT ON emp2
3 FOR EACH ROW
4 BEGIN
5 SELECT emp2_seq.NEXTVAL
6 INTO :NEW.col4
7 FROM DUAL;
8 END emp2_bir;
9 /
Trigger created.
SCOTT@orcl_11gR2> SHOW ERRORS
No errors.
-- add materialized view log, materialized view, and check constraint on materialized view:
SCOTT@orcl_11gR2> CREATE MATERIALIZED VIEW LOG ON emp2
2 WITH ROWID, SEQUENCE (col4), PRIMARY KEY INCLUDING NEW VALUES
3 /
Materialized view log created.
SCOTT@orcl_11gR2> CREATE MATERIALIZED VIEW emp2_mview
2 REFRESH FAST ON COMMIT
3 AS
4 SELECT COUNT (*) violations
5 FROM emp2 a, emp2 b
6 WHERE SUBSTR (a.col1, 1, 1) = 'X'
7 AND SUBSTR (b.col1, 1, 1) != 'X'
8 AND SUBSTR (a.col1, 2) = SUBSTR (b.col1, 2)
9 AND a.col4 > b.col4
10 /
Materialized view created.
SCOTT@orcl_11gR2> ALTER TABLE emp2_mview
2 ADD CONSTRAINT emp2_view_ck
3 CHECK (violations = 0)
4 /
Table altered.
-- test inserts:
SCOTT@orcl_11gR2> INSERT INTO emp2 (col1) VALUES ('A02')
2 /
1 row created.
SCOTT@orcl_11gR2> COMMIT
2 /
Commit complete.
SCOTT@orcl_11gR2> INSERT INTO emp2 (col1) VALUES ('A03')
2 /
1 row created.
SCOTT@orcl_11gR2> COMMIT
2 /
Commit complete.
SCOTT@orcl_11gR2> INSERT INTO emp2 (col1) VALUES ('B03')
2 /
1 row created.
SCOTT@orcl_11gR2> COMMIT
2 /
Commit complete.
SCOTT@orcl_11gR2> INSERT INTO emp2 (col1) VALUES ('X02')
2 /
1 row created.
SCOTT@orcl_11gR2> COMMIT
2 /
COMMIT
*
ERROR at line 1:
ORA-12008: error in materialized view refresh path
ORA-02290: check constraint (SCOTT.EMP2_VIEW_CK) violated
SCOTT@orcl_11gR2> INSERT INTO emp2 (col1) VALUES ('X03')
2 /
1 row created.
SCOTT@orcl_11gR2> COMMIT
2 /
COMMIT
*
ERROR at line 1:
ORA-12008: error in materialized view refresh path
ORA-02290: check constraint (SCOTT.EMP2_VIEW_CK) violated
SCOTT@orcl_11gR2> INSERT INTO emp2 (col1) VALUES ('X04')
2 /
1 row created.
SCOTT@orcl_11gR2> COMMIT
2 /
Commit complete.
SCOTT@orcl_11gR2> INSERT INTO emp2 (col1) VALUES ('X05')
2 /
1 row created.
SCOTT@orcl_11gR2> COMMIT
2 /
Commit complete.
SCOTT@orcl_11gR2> INSERT INTO emp2 (col1) VALUES ('A04')
2 /
1 row created.
SCOTT@orcl_11gR2> COMMIT
2 /
Commit complete.
SCOTT@orcl_11gR2> INSERT INTO emp2 (col1) VALUES ('A05')
2 /
1 row created.
SCOTT@orcl_11gR2> COMMIT
2 /
Commit complete.
-- results:
SCOTT@orcl_11gR2> SELECT * FROM emp2
2 /
COL1 COL2 COL3 COL4
---- ---- ---- ----------
A02 1
A03 2
B03 3
X04 6
X05 7
A04 8
A05 9
7 rows selected.
SCOTT@orcl_11gR2> SELECT * FROM emp2_mview
2 /
VIOLATIONS
----------
0
1 row selected.
SCOTT@orcl_11gR2>
|
|
|
|
|
|
|
|
|
|
| Re: ORA-04091,ORA-06512 [message #478021 is a reply to message #478014] |
Tue, 05 October 2010 13:19 |
Solomon Yakobson
Messages: 3273
Registered: January 2010
Location: Connecticut, USA
|
Senior Member |
|
|
Barbara Boehmer wrote on Tue, 05 October 2010 12:57I think the package and triggers method also doesn't raise an error until a commit is issued. What would you suggest instead?, a procedure?
Trigger solution, in general, not possible at all. Trigger is transactional - it will not see uncommitted changes made by other sessions. What Maaher is suggesting is how to bypass mutating table error. What OP wants is to implemet table with certain logic between table rows. In most cases it is misunderstanding of relational tables and as a result bad design. I did not follow OP's post closely, but it looks like it is classic case where we want to make sure quantity being ordered is in stock. It is a complex task with many knows solutions. In retail, AFAIK, most popular ones are "better safe than sorry" solutions with pre-allocating product upfront and telling customer something like "temporarily out of stock". If customer opts out quantity is returned back into the pool.
SY.
|
|
|
|
| Re: ORA-04091,ORA-06512 [message #478055 is a reply to message #478021] |
Wed, 06 October 2010 00:20 |
 |
Michel Cadot
Messages: 68641
Registered: March 2007
Location: Nanterre, France, http://...
|
Senior Member
Account Moderator |
|
|
Quote:In retail, AFAIK, most popular ones are "better safe than sorry" solutions with pre-allocating product upfront and telling customer something like "temporarily out of stock". If customer opts out quantity is returned back into the pool.
I concur, this is what is implemented in the retail customers I worked with.
Regards
Michel
[Updated on: Wed, 06 October 2010 02:13] Report message to a moderator |
|
|
|
|
|
|
|
Goto Forum:
Current Time: Fri Apr 19 08:08:44 CDT 2024
|
] 
Blog
Search
Help
Members
Register
Login
Home
