RE: EXEC pROCEDURE IN TRIGGER

From: Pakhutkin, Maxim (Max) <maxim.pakhutkin_at_paetec.com>
Date: Fri, 1 Jul 2005 13:53:34 -0400
Message-ID: <4C4E171C33ADBF43BFFE42978E1708C90E9511@mail1-corp.corp.paetec.com>

It sounds to me that you meant to say "reparse" instead of "recompile". If that is true, the following may be the doc you are looking for (with tests done on a 9i database):

http://asktom.oracle.com/pls/ask/f?p=4950:8:15302487144632085181::NO::F4950_P8_DISPLAYID,F4950_P8_CRITERIA:7507706875149

Max Pakhutkin

-----Original Message-----
From: oracle-l-bounce_at_freelists.org
[mailto:oracle-l-bounce_at_freelists.org]On Behalf Of Guang Mei
Sent: Wednesday, June 29, 2005 10:06 AM
To: oracle-l_at_freelists.org
Subject: RE: EXEC pROCEDURE IN TRIGGER

I have read somewhere that when writing a trigger, one should not put all the code in the trigger, instead one should create a store procedure and call it from the trigger. Doing so would improve the performance, becuase oracle will re-compile the trigger code everytime when a trigger is fired. My question is if there is any paper/doc/study exist somewhere that compare the performance. For a typical trigger with say about 20 lines of code, how much improvement could one expect if putting the code into a procedure. I guess I could do some test on the system by myself (too lazy), but wondering if I can get some rough idea. I looked around on the web but can not find any detailed paper out there.

Thanks.

Guang

-----Original Message-----
From: oracle-l-bounce_at_freelists.org
[mailto:oracle-l-bounce_at_freelists.org]On Behalf Of Hallas, John, Tech
Dev
Sent: Wednesday, June 29, 2005 6:36 AM
To: oracle-l_at_freelists.org
Subject: RE: EXEC pROCEDURE IN TRIGGER

Oracle does not allow a commit (or rollback or savepoint)in a trigger. Your procedure must have an implicit commit because of the use of DDL. This is allowed in SLQ obviously but explains why it fails when called from a trigger.

What exactly is the contents of the procedure. Can you work around it?

John

PRIVILEGED AND CONFIDENTIAL:
This communication, including attachments, is for the exclusive use of addressee and may contain proprietary, confidential and/or privileged information. If you are not the intended recipient, any use, copying, disclosure, dissemination or distribution is strictly prohibited. If you are not the intended recipient, please notify the sender immediately by return e-mail, delete this communication and destroy all copies.

--
http://www.freelists.org/webpage/oracle-l
--
http://www.freelists.org/webpage/oracle-l

Received on Fri Jul 01 2005 - 13:58:57 CDT