Oracle FAQ | Your Portal to the Oracle Knowledge Grid |
Home -> Community -> Usenet -> c.d.o.misc -> 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