Need workaround for Oracle Mutating table
swapni...@gmail.com
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.
Mark D Powell
If you have Oracle support you can go to metalink and look for a white
paper on this subject that uses a statement level trigger along with
the row level trigger to work around mutating table errors. The
developers I work with have not had much luck using the technique. You
can also search the asktom site on this as Tom has an example of the
work around.
In case where there is only one or two valid sources for inserts or
updates as the case may be I ususally suggest using a stored procedure
to perform the update process. The procedure can select from the table
prior to performing the insert. Usually a row level trigger, check
constraint, or even not null constraint can be used to ensure that the
logic was performed prior to the insertion.
HTH -- Mark D Powell --
Jim Smith
swapni...@gmail.com writes
columns?
begin
insert ... -- original values
exeception
when dup_val_on_index then
insert ... -- replacement values
end;
--
Jim Smith
Ponder Stibbons Limited <http://oracleandting.blogspot.com/>
RSS <http://oracleandting.blogspot.com/atom.xml>
DA Morgan
What creates a mutating table error changes with the version so read
the docs at tahiti.oracle.com. But most likely this can be solved by
creating the function as an autonomous transaction.
Go to Morgan's Library at www.psoug.org and scroll down to 'Autonomous
Transaction'.
--
Daniel A. Morgan
University of Washington
damo...@x.washington.edu
(replace x with u to respond)
Puget Sound Oracle Users Group
www.psoug.org
Anurag Varma
There is a reason why oracle throws a mutating table error.
Consider a case when your session is entering the second row
which it believes should set the value to 0 and another session
is deleting that first row.
What happens then?
You could potentially workaround mutating table error using autonomous
transactions. However, the real issue is of a bad design. You need to
reconsider your design and for once imagine that a database can
support concurrent transactions.
Your issue can more simply be fixed by using a view over that table
which will return 0 for subsequent rows as per the required design.
Anurag
swapni...@gmail.com
Hi Jim,
Thanks for the reply.
The concern was not check for duplicate index.
The requirement is there could be multiple records with the key
<compoiste primary key>. one of the primary key is a sequence number.
As there could be multiple records with the same key on the same date,
the sequence number changes.<next record>
My requirement is if there exists a record on the same day, <except for
the first record. so we are checking record_count>0> the subsequent
records for the same day have some charges as 0. Those charges should
not get repeated as the first record already has it,
Jim Smith
swapni...@gmail.com writes
dependent on the primary key, they shouldn't be in that table.
You should have a daily charges table keyed on (<whatever>+date) and the
table you described keyed on (<whatever>+date+sequence). Alternatively,
you could key it on (<whatever>+datetime) and drop the sequence.
DA Morgan
As Jim Smith indicates ... the fault here is with the data model. What
you are describing is modeled incorrectly. Reconsider this as a parent
child relationship where the first record is the parent of all
subsequent, detail, records.
swapni...@gmail.com
referred the "Autonomous Transaction" and thats what i needed. <I'll
definately think over remodelling it.>
Regards,
Swapnil
hast...@hotmail.com
> Thanks DA Morgan. It solved the problem. Thanks a lot for the help. I
> referred the "Autonomous Transaction" and thats what i needed. <I'll
> definately think over remodelling it.>
>
> Regards,
> Swapnil
Swapnil, it is rare that an autonomous transaction is a
solution to the mutating table warning...
Could we see your solution ?
--- Raoul
DA Morgan
hast...@hotmail.com
> hasta...@hotmail.com wrote:
> > swapnil.k...@gmail.com a écrit :
> >> Thanks DA Morgan. It solved the problem. Thanks a lot for the help. I
> >> referred the "Autonomous Transaction" and thats what i needed. <I'll
> >> definately think over remodelling it.>
>
> > solution to the mutating table warning...
>
> > Could we see your solution ?
>
> --
Daniel, what is the exact specification of the code in tour mutating
table
example ? Is t2 supposed to record the history of the number of rows
of t2 ?
--- Raoul
hast...@hotmail.com
> On 23 jan, 18:30, DA Morgan <damor...@psoug.org> wrote:
>
> > hasta...@hotmail.com wrote:
> > > swapnil.k...@gmail.com a écrit :
> > >> Thanks DA Morgan. It solved the problem. Thanks a lot for the help. I
> > >> referred the "Autonomous Transaction" and thats what i needed. <I'll
> > >> definately think over remodelling it.>
>
> > > Swapnil, it is rare that an autonomous transaction is a
> > > solution to themutatingtablewarning...
>
> > > Could we see your solution ?
>
> > You can find one example at:http://www.psoug.org/reference/table_trigger.html
>
> example ? Is t2 supposed to record the history of the number of rows
> of t2 ?
Erratum : Is t2 supposed to record the history of the number of
rows of t1 ?
DA Morgan
In theory it should, AFTER INSERT, record the number of records it found
in T1, the table on which it is located, into T2 for auditing purposes.
It is a rotten piece of code but it demonstrates the concept.
hast...@hotmail.com
> hasta...@hotmail.com wrote:
> > On 23 jan, 18:30, DA Morgan <damor...@psoug.org> wrote:
> >> hasta...@hotmail.com wrote:
> >>> swapnil.k...@gmail.com a écrit :
> >>>> Thanks DA Morgan. It solved the problem. Thanks a lot for the help. I
> >>>> referred the "Autonomous Transaction" and thats what i needed. <I'll
> >>>> definately think over remodelling it.>
> >>> Swapnil, it is rare that an autonomous transaction is a
> >>> solution to the mutating table warning...
> >>> Could we see your solution ?
> >> You can find one example at:http://www.psoug.org/reference/table_trigger.html
>
> > table example ? Is t2 supposed to record the history of the number of rows
> > of t2 ?
>
>
> It is a rotten piece of code but it demonstrates the concept.
And it does not fulfill the specification, Daniel,
as you know already.
truncate table t1;
truncate table t2;
insert into t1(x) values (1);
commit;
select count(*) from t1;
COUNT(*)
----------
1
select * from t2;
X
----------
0
Solutions using autonomous transaction to go around
the mutating table problem almost always fail to meet
their spec, especially in the face of rollbacks and/or
concurent updates and/or multiple rows DML statements.
I strongly suspect that I would have been able to
break the OP code if he had posted it, and that
the pattern http://asktom.oracle.com/tkyte/Mutate/index.html
would have worked much better for him
Have a nice day (sunny over here :-))
--- Raoul
Oradba Linux
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.
Anurag Varma
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
arup...@gmail.com
this list, which explains the delay in the response. I am the author
of the MetaLink Note. There are a few things I need to say about the
note.
(1) It was written 12 years ago, in 1995, for Oracle 7.1. That's
right; 12 years ago. Those days there was no MetaLink. There was this
focurm on CompuServe (old timers will probably remember that,
especially the fourm called "orauser"). Another forum was called
"orasupport", the precursor to the present day MetaLink. When MetaLink
appeared, Oracle placed some of the docs (even from customers) on
MetaLink.
(2) I wrote the contents in response to a member's question. I also
presented the same solution in Oracle World that year. My presentation
was detailed; but the response was not. The response was highly
contextual and illustrious in nature to demonstrate a way to resolve a
thorny issue. There was no intention of going into all possible
combinations of the problem and how they would all play out. My
presentation did that, however.
(3) The article is, well, 12 years old. Today the solution could be
much simpler, e.g. use of autonomous transaction
So, Anurag, I hope I have presented the case to why this should not by
considered to be "pity" for what I wrote.
Regards,
Arup
Anurag Varma
Arup,
With all the details on hand, my criticism was probably not worded
well.
I do however, want to tell you that I meant no offense and that I
consider
your technical writings to be of the highest degree.
That article however I did not agree with ... and since it comes quite
high in
metalink search, maybe it needs to be updated a little bit.
Cheers,
Anurag