Re: Need workaround for Oracle Mutating table

On Feb 3, 10:49 pm, Oradba Linux <oradba_li..._at_verizon.net> wrote:
> swapnil.k..._at_gmail.com wrote:
> > Hi there,
>
> > I wish to insert two values as 0 if there exists a similar record in
> > the database. Here goes the code for the same.
>
> > The trigger is like this:
>
> > CREATE OR REPLACE TRIGGER BEFORE_CHANGE_SVM
> > BEFORE INSERT OR UPDATE
> > ON ST_VOL_MNTH
> > FOR EACH ROW
> > REC := RY_CGE(:NEW.X,:NEW.Y,:NEW.Z);
> > --the function is defined below.
> > IF (REC>0) THEN
> > -set the new value.abc :=0;
> > END IF;
> > END;
>
> > Where the function is like this. which returns the number of records
> > present in the table matching the criterea,
>
> > SELECT COUNT(*) INTO REC FROM ST_VOL_MNTH SVM
> > where values are matching.
>
> > I'm getting the mutation error as i'm trying to query the same table on
> > which the trigger is written.
> > It would be great if somebody can help!
>
> > Thanks in advance.
> > Swapnil Kale.
>
> There is a white paper in metalink published by Arup Nanda which
> explains the workaround in good detail. He is pretty good at what he
> writes. He was the "DBA of the Year" by Oracle Magazine in 2003 or 2004.
> The developers at our site use that workaround and it has been working
> very well.

hmm ... I saw that code. Its a pity Arup Nanda fails to point out what problems end user might encounter if he/she tries to implement even his specific workaround.

I'm not going to print his note or his code here .. (one can find it by doing a metalink search "arup nanda mutating"), however here is one way it will not work:
ORA92> select * from salgrade;

     GRADE LOSAL HISAL START_DATE END_DATE

---------- ---------- ---------- ------------------------
------------------------
         1       1000       2000 01-APR-94 00:00:00       03-AUG-95
00:00:00
         1       1200       2200 03-AUG-95 00:00:00
         2       1500       3000 23-JUL-92 00:00:00       12-DEC-93
00:00:00
         2       1600       3200 12-DEC-93 00:00:00       11-JAN-95
00:00:00
         2       1800       3400 11-JAN-95 00:00:00

As per his code, one should not be able to enter a row which has a date in between start /end date. Here is what happens if two sessions try to enter a valid date which for each session is a valid row, but the final result ends up breaking the main rule:

SESSION 2> insert into salgrade values (2, 9000, 100000, '26- jan-95',null);

SESSION 1> insert into salgrade values (2, 9000, 100000, '25- jan-95',null);

SESSION 2> commit;
SESSION 1> commit;

ORA92> select * from salgrade;

     GRADE LOSAL HISAL START_DATE END_DATE

---------- ---------- ---------- ------------------------
------------------------
         1       1000       2000 01-APR-94 00:00:00       03-AUG-95
00:00:00
         1       1200       2200 03-AUG-95 00:00:00
         2       1500       3000 23-JUL-92 00:00:00       12-DEC-93
00:00:00
         2       1600       3200 12-DEC-93 00:00:00       11-JAN-95
00:00:00
         2       1800       3400 11-JAN-95 00:00:00       25-JAN-95
00:00:00
         2       9000     100000 20-JAN-95 00:00:00
         2       9000     100000 25-JAN-95 00:00:00

7 rows selected.

There .. I was able to enter a row which had a start date "20-jan-95" which now lies between '11-jan-95' and '25-jan-95' . Not only that, I have corrupt data with two rows having no end_dates.

It was a workaround all right .. but one should mention how easily it can be broken in a multi-session environment.

Anurag Received on Mon Feb 05 2007 - 15:45:15 CST