Hello,
Are u performing any exception handeling in Ur
code? Try to capture oracle err mesg using
"USER DEFINED EXCEPTIONS". For more detail look
into "DBMS_ERROR_CODE" and "DBMS_ERROR_TEXT "
OraCOP
- Charu Joshi <joshic_at_mahindrabt.com> wrote:
> Hi Arup,
>
> Thanks for the reply.
>
> I have checked the trigger code thoroughly and
> surely it's not a mutating
> trigger. Nor is there any cascade effect or the
> trace files would have shown
> the statements within that trigger.
>
> There is some other error occurring, but we are not
> able to trap it because
> the front-end application just doesn't do error
> trapping.
>
> Is there any way to see the error occurred within
> another session? We are
> checking trace logs but they don't show any error.
>
> Thanks & regards,
> Charu
>
> -----Original Message-----
> Sent: Thursday, November 21, 2002 3:32 PM
> To: Multiple recipients of list ORACLE-L
> required (Distributed transaction)
>
> Charu,
>
> I guess the error you are getting is "Table or
> Trigger is mutating". and
> perhaps you are using an after insert row trigger.
> This is a common and
> expected problem. There are ways to get around it.
> You have not mentioned
> the version of Oracle; I would assume 8.1.7 or
> later.
>
> Inside an after insert row trigger, you can not
> operate on the table on
> which trigger is defined. for instance in your case
> the trigger (say,
> TRA_LT) on the table LT can't insert into LT inside
> it. However if you
> define the same in a BEFORE INSERT row trigger, it's
> allowed.
>
> Caution, though, the insert through the trigger will
> fire the BEFORE INSERT
> trigger too, which in turn will insert a row in LT
> and then the trigger
> fires again..a vicious cycle. Fortuanately Oracle
> breaks the cascading
> trigger after 17 atetmpts. So, you need to have a
> limiting condition while
> defining the trigger WHEN (NEW.STATUS='OLD') or
> similar to make sure you can
> differentiate between the trigger inserted and the
> user inserted rows.
>
> Finally, if you insert into the table LT using a
> construct like INSERT INTO
> LT VALUES (...) it works; but if you use INSERT INTO
> LT SELECT .. FROM
> ANOTHER_TABLE, it does not. this is not documented
> anywhere; I found this by
> accident.
>
> Hope this helps.
>
> Arup Nanda
> www.proligence.com
>
> ----- Original Message -----
> To: "Multiple recipients of list ORACLE-L"
> <ORACLE-L_at_fatcity.com>
> Sent: Thursday, November 21, 2002 9:18 AM
> (Distributed transaction)
>
> > Gurus,
> >
> > Desperately need some ideas to solve this one.
> >
> > Following is the sequence of events as it happens:
> >
> > 1. Front-end application (VC++) queries from a
> view V. V fetches data from
> a
> > remote database RD via a database link.
> > 2. Front-end inserts a row in a local table LT.
> > 3. The Insert trigger on this table queries view V
> to take a final stock.
> > 4a. If conditions match, the trigger inserts in
> remote table RT on RD. NO
> > PROBLEM in condition.
> > 4b. If conditions don't match, the front-end
> application tries to insert
> > another row in LT. The trigger starts again and
> tries to insert row in RT.
> > At this point we SEEM to get the error.
> >
> > We can't get anybody to debug the front-end
> application. We tried setting
> > SQL_TRACE TRUE, and the only thing made clear was
> that the trigger didn't
> go
> > beyond trying to insert into RT.
> >
> > We tried replicating the whole scenario by
> executing each and every
> > statement that the front-end fires through
> SQL*Plus as a script, and it
> > doesn't give any error.
> >
> > I realize that I have given only a fraction of the
> whole information, but
> > what is the best way to debug this situation?
> >
> > Are there any do's don'ts in distributed
> transactions which our code may
> not
> > be following?
> >
> > Thanks in advance,
> > Charu
> >
>
>
> Disclaimer
>
> This message (including any attachments) contains
> confidential information intended for a specific
> individual and purpose, and is protected by law.
> If you are not the intended recipient, you should
> delete this message and are hereby notified that
> any disclosure, copying, or distribution of this
> message, or the taking of any action based on it,
> is strictly prohibited.
>
>
> Visit us at http://www.mahindrabt.com
>
>
>
> --
> Please see the official ORACLE-L FAQ:
> http://www.orafaq.com
> --
> Author: Charu Joshi
> INET: joshic_at_mahindrabt.com
>
> Fat City Network Services -- 858-538-5051
> http://www.fatcity.com
> San Diego, California -- Mailing list and web
> hosting services
>
> To REMOVE yourself from this mailing list, send an
> E-Mail message
> to: ListGuru_at_fatcity.com (note EXACT spelling of
> 'ListGuru') and in
> the message BODY, include a line containing: UNSUB
> ORACLE-L
> (or the name of mailing list you want to be removed
> from). You may
> also send the HELP command for other information
> (like subscribing).
Do you Yahoo!?
Yahoo! Mail Plus - Powerful. Affordable. Sign up now.
http://mailplus.yahoo.com
--
Please see the official ORACLE-L FAQ: http://www.orafaq.com
--
Author: OraCop
INET: oracop_at_yahoo.com
Fat City Network Services -- 858-538-5051 http://www.fatcity.com
San Diego, California -- Mailing list and web hosting services
---------------------------------------------------------------------
To REMOVE yourself from this mailing list, send an E-Mail message
to: ListGuru_at_fatcity.com (note EXACT spelling of 'ListGuru') and in
the message BODY, include a line containing: UNSUB ORACLE-L
(or the name of mailing list you want to be removed from). You may
also send the HELP command for other information (like subscribing).
Received on Tue Nov 26 2002 - 18:23:56 CST
