I am supporting an application running on Oracle Database 11g Enterprise Edition Release 184.108.40.206.0 – 64-bit production.
We have a table that stores data per hour that uses triggers to call a package to synchronize with a monthly table that stores a total amount.
When the table is updated per hour, the update trigger saves the previous quantity in a table package variable in this format: primary key fields, quantity.
TYPE HOURLY_CHANGE_TAB IS TABLE OF HOURLY_REC INDEX BY BINARY_INTEGER;
TYPE HOURLY_REC IS RECORD (
The amount is set to -old_amount. Then add the new quantity to the quantity. It does this for each time record that is updated. Then, if there are 120 hours in a month, you will subtract the previous amount 120 times from the variable and add the new amount 120 times to get the total change. A subsequent declaration trigger or a manual call would update the month record with this total and erase the package variable. When we are updating a large number of hours, we set an indicator to disable the month update and then manually call the update method once at the end to get performance. However, the problem has already happened when this method is called.
When I update an agreement that runs for several years, the total amount is canceled for a new amount, the previous amount in a random number of months. So, if the previous amount was 100 per hour and the new one is 10 per hour, then the amount we take to the month's record is -90 what it should be. Most months would be correct. I have seen zero, one, two and four months interrupted after each update / rollback test run. Every time I run exactly the same update and then go back after checking the error.
I recorded every time the package variable changes to a new table and I saw the following:
KEY and SUB_KEY are keys in this example. The month is the monthly record of which our time is part. The quantity column shows the quantity that will be added to the package variable. The index is the index of the table variable that we are updating for this row. Initial amount is the value of the quantity column at the beginning of the update log. Calc Amount is what the package variable should store after adding the quantity to the initial quantity. Final quantity is the value recovered from the package variable after saving the update. The row index is the only incremental sequence in the log table to track the order in which the updates were executed. The correct quantity is a window function that adds the quantity ordered by row_index, divided by the keys, to show what the package variable should store in each row.
Observe what happens between rows 2 and 3. The final quantity in 2 is the correct value of 540. The initial quantity in 3 is 550, displaced by the new volume (20) – previous volume (10). This ratio was maintained for an update of 100 to 10, the amount shifted was -90, not 10. And yet, there is no gap in the index of the row. We go directly from 16188 to 16189 without a gap. I created a copy of the package and deleted everything except the method to update the package variable. There is no other route to update the table variable, except through the method that is registered. The package variable is defined within the package body, so no other package can update it, and I removed all other methods in the package, except the one used to update it. I cannot understand how this variable is changing between the end of one method and the beginning of the next.
CREATE OR REPLACE PACKAGE BODY TEST_PKG AS
PKG_HOUR_COUNT NUMBER := 0;
HOUR_TAB HOURLY_CHANGE_TAB ;
Again, the number of months affected by this problem is random, and all are divided by the same amount, one month is not compensating another with an error.
Note that I have endeavored to reduce the following code to a minimum version of the code. The names of the tables and columns have been changed. If I try to execute the activation code in a loop instead of executing an update, the problem does not occur.
I was able to create a new version of the package, remove everything that was not related to these calls and reproduce the problem and I was successful. I tried to create a mini copy of the table with only the rows I needed and I may have been able to reproduce the problem once. I know it's vague, but it hasn't worked since then on the copied mini table.
SUB_KEY is not used in the package variable, since it only defines the hours in this table, so all hours with the same KEY and SUB_KEy will be in the same month, regardless of SUB_SUB_KEY.
CREATE TABLE HOURS
(KEY NUMBER(12,0) NOT NULL ENABLE,
SUB_KEY NUMBER(6,0) NOT NULL ENABLE,
SUB_SUB_KEY NUMBER(3,0) NOT NULL ENABLE,
GMT_TIME DATE NOT NULL,
DAY DATE NOT NULL ENABLE,
HOUR VARCHAR2(4 BYTE) NOT NULL ENABLE,
AMOUNT NUMBER(10,3) NOT NULL ENABLE,
CONFIRMED_FLAG VARCHAR2(1 BYTE) DEFAULT 'N' NOT NULL ENABLE,
CONSTRAINT HOUR_PK PRIMARY KEY (KEY, SUB_KEY, SUB_SUB_KEY, GMT_TIME)
) COMPRESS FOR OLTP
BUFFER_POOL DEFAULT FLASH_CACHE DEFAULT CELL_FLASH_CACHE DEFAULT)
PARTITION BY RANGE ("DY_DAY") INTERVAL (NUMTOYMINTERVAL(3,'MONTH')));
CREATE OR REPLACE TRIGGER UPDATE_HOUR BEFORE
UPDATE OF AMOUNT
FOR EACH ROW
IF :NEW.CONFIRMED_FLAG = 'Y' AND :OLD.CONFIRMED_FLAG = 'Y'
THEN RAISE_APPLICATION_ERROR(-20914, 'You cannot Update.');
IF :NEW.CONFIRMED_FLAG = 'A' THEN
:NEW.CONFIRMED_FLAG := 'Y';
IF TEST_PKG.GET_UPDATE_HOURS_FLAG = 'Y' THEN
TEST_PKG.UPDATE_HOURS(:OLD.KEY, :NEW.KEY, :OLD.SUB_KEY, :NEW.SUB_KEY, :OLD.SUB_SUB_KEY, :NEW.SUB_SUB_KEY, :OLD.DAY, :NEW.DAY, :OLD.AMOUNT, :NEW.AMOUNT);
CREATE TABLE HOURS_DATA(
Package trimmed, there is only one portion left that plays HOUR_TAB:
PROCEDURE UPDATE_HOUR_TAB (
AMOUNT NUMBER) IS
STARTING_AMOUNT number := 0;
CALC_AMOUNT number := 0;
CNT := HOUR_TAB.FIRST;
WHILE CNT IS NOT NULL LOOP
EXIT WHEN HOUR_TAB(CNT).KEY = KEY AND HOUR_TAB(CNT).SUB_KEY = SUB_KEY AND HOUR_TAB(CNT).MONTH = MONTH;
CNT := HOUR_TAB.NEXT(CNT);
IF CNT IS NULL THEN
PKG_HOUR_COUNT := PKG_HOUR_COUNT + 1;
HOUR_TAB(PKG_HOUR_COUNT).KEY := KEY;
HOUR_TAB(PKG_HOUR_COUNT).SUB_KEY := SUB_KEY;
HOUR_TAB(PKG_HOUR_COUNT).MONTH := MONTH;
HOUR_TAB(PKG_HOUR_COUNT).AMOUNT := AMOUNT;
STARTING_AMOUNT := HOUR_TAB(CNT).AMOUNT;
CALC_AMOUNT := HOUR_TAB(CNT).AMOUNT + AMOUNT;
HOUR_TAB(CNT).AMOUNT := HOUR_TAB(CNT).AMOUNT + AMOUNT;
IF CNT IS NULL THEN
CNT := PKG_HOUR_COUNT;
INSERT INTO HOURS_DATA
(SELECT COUNT(*) FROM HOURS_DATA),
Update I ran:
P_AMOUNT NUMBER := 20;
P_KEY NUMBER := XXXXXX;
FOR UPDATE_HOUR IN(SELECT * FROM HOURS
WHERE KEY = P_KEY
AND DAY >= '1-MAR-20'
SET AMOUNT = P_AMOUNT
WHERE KEY = P_KEY
AND DAY = UPDATE_HOUR.DAY
AND HOUR = UPDATE_HOUR.HOUR
AND (AMOUNT <> P_AMOUNT);
--Running the trigger code directly has never worked
-- IF :NEW.CONFIRMED_FLAG = 'Y' AND :OLD.CONFIRMED_FLAG = 'Y'
-- THEN RAISE_APPLICATION_ERROR(-20914, 'You cannot Update.');
-- IF :NEW.CONFIRMED_FLAG = 'A' THEN
-- :NEW.CONFIRMED_FLAG := 'Y';
-- END IF;
-- IF TEST_PKG.GET_UPDATE_HOURS_FLAG = 'Y' THEN
-- TEST_PKG.UPDATE_HOURS(:OLD.KEY, :NEW.KEY, :OLD.SUB_KEY, :NEW.SUB_KEY, :OLD.SUB_SUB_KEY, :NEW.SUB_SUB_KEY, :OLD.DAY, :NEW.DAY, :OLD.AMOUNT, :NEW.AMOUNT);
-- END IF;
-- END IF;
--DOES THE MONTHLY UPDATE BASED ON PACKAGE VARIABLE AND CLEARS THE VARIABLE
Code to verify records for errors:
SELECT HD.*, SUM(AMOUNT) OVER(PARTITION BY KEY, SUB_KEY, MONTH ORDER BY ROW_INDEX) CORRECT_AMOUNT, COUNT(*) OVER(PARTITION BY SUB_KEY) SEQ_COUNT
FROM HOURS_DATA HD
WHERE HD.TOTAL_AMOUNT <> CORRECT_TOTAL
ORDER BY ROW_COUNT;
I have checked to see if there are more rows than there should be with the same index or sub_key, but the number of rows of sub_keys and index_rows is always the same. At this moment my only hypothesis is an error in Oracle. I am pleased to provide more data or code if necessary, but since I have not been able to duplicate this in another table, I am not sure how to help another person reproduce it.
While you may be able to solve this by changing the way we update the monthly table, or update the monthly table after each update at one hour despite the cost of performance (there are triggers in the monthly table that make a cost estimate monthly for each update to be expensive), this problem is strange enough that I would like to solve it if possible.
Thank you for your help and patience to read this text wall.