Two methods for processing the variant table (ORA-04091) in Oracle

Two methods for processing the variant table (ORA-04091) in Oracle

Oracle often used trigger often appears ora-04091 variant table problem, here bring two kinds of processing methods for your reference.

-- ***** Example *****
Scenario Description:
-- 1. init table
Create table sales (prod_code varchar2 (4), amount_sold number );

Insert into sales values ('20170101', 0 );
Insert into sales values ('20170101', 0 );
Insert into sales values ('20170101', 0 );

Commit;

Select * from sales order by 1;

-- 2. If a subcategory is inserted or updated, the total sales volume of the parent category must be updated in cascade mode.
-- For example, if the sales volume of category 0111 is updated and the sales volume of Category 1 is increased, the sales volume of category 0100 must be increased by 1.
/* Test trigger:
Create or replace trigger sales_amount_refresh
After insert on sales
For each row
When (new. amount_sold> 0)
Declare
 
Begin
Update sales
Set amount_sold = amount_sold +: new. amount_sold
Where substr (: new. prod_code, 0, length (rtrim (prod_code, '0') =
Rtrim (prod_code, '0 ');
End;

*/

-- A variant table problem occurs.
/* Test SQL:

SQL> insert into sales values ('20140901', 1 );
 
Insert into sales values ('20140901', 1)
 
ORA-04091: table DEXTER. SALES is mutating, trigger/function may not see it
ORA-06512: at "DEXTER. SALES_AMOUNT_REFRESH", line 4
ORA-04088: error during execution of trigger 'dexter. SALES_AMOUNT_REFRESH'
*/

-- Solution 1: You can use a self-made transaction to solve the problem, but with a bug, you cannot roll back.

/*
Create or replace trigger sales_amount_refresh
After insert on sales
For each row
When (new. amount_sold> 0)
Declare
Pragma autonomous_transaction;
Begin
Update sales
Set amount_sold = amount_sold +: new. amount_sold
Where substr (: new. prod_code, 0, length (rtrim (prod_code, '0') =
Rtrim (prod_code, '0 ');
Commit;
End;
The statements in the autonomous transaction will not be rolled back.
*/

-- Solution 2
Use the collection variables of the package to record the information to be changed, and then use the statement Level trigger to perform the update operation. The variables in the package are only valid in the session.
-- You only need to create a package header.

Create or replace package pkg_vars is
Type c_t is table of number index by varchar2 (4 );
Refresh_sales_list c_t;
End;
/
-- Modify the row-Level Trigger
Create or replace trigger sales_amount_refresh
After insert on sales
For each row
When (new. amount_sold> 0)
Declare
Begin
If length (rtrim (: new. prod_code, '0')> 2 then
Pkg_vars.refresh_sales_list (: new. prod_code): =: new. amount_sold;
End if;
End;
/
-- Adds a statement-Level Trigger.
Create or replace trigger sales_amount_refresh_statement
After insert on sales
Declare
Ind varchar2 (4 );
Begin
Ind: = pkg_vars.refresh_sales_list.first;
WHILE ind IS NOT NULL LOOP
Update sales
Set amount_sold = amount_sold + pkg_vars.refresh_sales_list (ind)
Where substr (ind, 0, length (rtrim (prod_code, '0') =
Rtrim (prod_code, '0') and length (rtrim (prod_code, '0') = 2;
Ind: = pkg_vars.refresh_sales_list.NEXT (ind );
End loop;
Pkg_vars.refresh_sales_list.delete;
End;
/

ORA-00600 [2662] troubleshooting

Troubleshooting for ORA-01078 and LRM-00109

Notes on ORA-00471 Processing Methods

ORA-00314, redolog corruption, or missing Handling Methods

Solution to ORA-00257 archive logs being too large to store