From oracle-l-bounce@freelists.org  Tue Aug 23 09:21:42 2005
Return-Path: <oracle-l-bounce@freelists.org>
Received: from air891.startdedicated.com (root@localhost)
 by orafaq.com (8.12.10/8.12.10) with ESMTP id j7NELgNe001833
 for <oracle-l@orafaq.com>; Tue, 23 Aug 2005 09:21:42 -0500
X-ClientAddr: 206.53.239.180
Received: from turing.freelists.org (freelists-180.iquest.net [206.53.239.180])
 by air891.startdedicated.com (8.12.10/8.12.10) with ESMTP id j7NELZIP001800
 for <oracle-l@orafaq.com>; Tue, 23 Aug 2005 09:21:36 -0500
Received: from localhost (localhost [127.0.0.1])
 by turing.freelists.org (Avenir Technologies Mail Multiplex) with ESMTP id 6CCEE1E5E1A;
 Tue, 23 Aug 2005 09:21:18 -0500 (EST)
Received: from turing.freelists.org ([127.0.0.1])
 by localhost (turing [127.0.0.1]) (amavisd-new, port 10024)
 with ESMTP id 08835-06; Tue, 23 Aug 2005 09:21:18 -0500 (EST)
Received: from turing (localhost [127.0.0.1])
 by turing.freelists.org (Avenir Technologies Mail Multiplex) with ESMTP id D84211E5E37;
 Tue, 23 Aug 2005 09:21:17 -0500 (EST)
Message-ID: <430B30AC.9060409@allegientsystems.com>
Date: Tue, 23 Aug 2005 10:20:28 -0400
From: Mladen Gogala <mgogala@allegientsystems.com>
User-Agent: Mozilla Thunderbird 1.0 (Windows/20041206)
X-Accept-Language: en-us, en
MIME-Version: 1.0
To: breitliw@centrexcc.com
Cc: Dave.Herring@acxiom.com,
        "Oracle-L@Freelists. Org (E-mail)" <oracle-l@freelists.org>
Subject: Re: Debug DDL trigger
References: <7ED53A68952D3B4C9540B4EFA5C76E36CB875F@CWYMSX04.Corp.Acxiom.net> <430B2639.2000905@centrexcc.com>
In-Reply-To: <430B2639.2000905@centrexcc.com>
Content-Type: multipart/alternative; boundary="------------050507050508070101070401"
X-archive-position: 24345
X-ecartis-version: Ecartis v1.0.0
Sender: oracle-l-bounce@freelists.org
Errors-To: oracle-l-bounce@freelists.org
X-original-sender: mgogala@allegientsystems.com
Precedence: normal
Reply-To: mgogala@allegientsystems.com
X-list: oracle-l
X-Virus-Scanned: by amavisd-new-20030616-p9 (Debian) at avenirtech.net
X-mailscan-MailScanner-Information: Please contact the ISP for more information
X-mailscan-MailScanner: Found to be clean
X-MailScanner-From: oracle-l-bounce@freelists.org
X-Spam-Level: 
X-Spam-Checker-Version: SpamAssassin 2.63 (2004-01-11) on 
 air891.startdedicated.com
X-Spam-Status: No, hits=-3.2 required=5.0 tests=AWL,BAYES_00,
 HTML_FONTCOLOR_BLUE,HTML_MESSAGE autolearn=no version=2.63
--------------050507050508070101070401
Content-Type: text/plain; charset=ISO-8859-1; format=flowed
Content-Transfer-Encoding: 7bit

Wolfgang Breitling wrote:

> I could be wrong, but I am pretty sure you are not allowed to issue 
> commits - or rollbacks - in a trigger. That rules out not only the 
> commits of your inserts, but especially your "execute immediate 
> 'create table ...'" as all DDL imply a commit.

Wolfgang, you're never wrong. If it appears that you are wrong,  it must 
be the work of Satan. In this case, however,
I can strenghten tour belief with an excerpt from 9i Application 
developers guide:


      Restrictions on Creating Triggers

Coding triggers requires some restrictions that are not required for 
standard PL/SQL blocks. The following sections discuss these restrictions.


        Maximum Trigger Size

The size of a trigger cannot be more than 32K.


        SQL Statements Allowed in Trigger Bodies

The body of a trigger can contain DML SQL statements. It can also 
contain |SELECT| statements, but they must be |SELECT|... |INTO|... 
statements or the |SELECT| statement in the definition of a cursor.

DDL statements are not allowed in the body of a trigger. Also, no 
transaction control statements are allowed in a trigger. |ROLLBACK|, 
|COMMIT|, and |SAVEPOINT| cannot be used.For system triggers, 
{|CREATE|/|ALTER|/|DROP|} |TABLE| statements and |ALTER|...|COMPILE| are 
allowed.

------------------------------------------------------------------------



-- 
Mladen Gogala
Oracle DBA
Ext. 121


--------------050507050508070101070401
Content-Type: text/html; charset=ISO-8859-1
Content-Transfer-Encoding: 7bit

<!DOCTYPE html PUBLIC "-//W3C//DTD HTML 4.01 Transitional//EN">
<html>
<head>
  <meta content="text/html;charset=ISO-8859-1" http-equiv="Content-Type">
</head>
<body bgcolor="#ffffff" text="#000000">
Wolfgang Breitling wrote:
<blockquote cite="mid430B2639.2000905@centrexcc.com" type="cite">I
could be wrong, but I am pretty sure you are not allowed to issue
commits - or rollbacks - in a trigger. That rules out not only the
commits of your inserts, but especially your "execute immediate 'create
table ...'" as all DDL imply a commit.
  <br>
</blockquote>
Wolfgang, you're never wrong. If it appears that you are wrong,&nbsp; it
must be the work of Satan. In this case, however,<br>
I can strenghten tour belief with an excerpt from 9i Application
developers guide:<br>
<h3 class="H2"><font color="#330099" face="Arial, Helvetica, sans-serif">Restrictions
on Creating Triggers</font></h3>
<!--/TOC=h2-->
<a name="1799"></a>
<p class="BP">Coding triggers requires some restrictions that are not
required for standard PL/SQL blocks. The following sections discuss
these restrictions.</p>
<a name="12438"></a>
<h4 class="SH1"><font face="Arial, Helvetica, sans-serif">Maximum
Trigger Size</font></h4>
<a name="12447"></a>
<p class="BP">The size of a trigger cannot be more than 32K.</p>
<a name="710"></a>
<h4 class="SH1"><font face="Arial, Helvetica, sans-serif">SQL
Statements Allowed in Trigger Bodies</font></h4>
<a name="712"></a>
<p class="BP">The body of a trigger can contain DML SQL statements. It
can also contain <code>SELECT</code> statements, but they must be <code>SELECT</code>...
<code>INTO</code>... statements or the <code>SELECT</code> statement
in the definition of a cursor.</p>
<a name="716"></a>
<p class="BP">DDL statements are not allowed in the body of a trigger.
Also, no transaction control statements are allowed in a trigger. <code>ROLLBACK</code>,
<code>COMMIT</code>, and <code>SAVEPOINT</code> cannot be used.For
system triggers, {<code>CREATE</code>/<code>ALTER</code>/<code>DROP</code>}
<code>TABLE</code> statements and <code>ALTER</code>...<code>COMPILE</code>
are allowed.</p>
<!--AnN_AnchorNote will not be supported in future releases -->
<a name="718"></a>
<hr><br>
<br>
<br>
<pre class="moz-signature" cols="72">-- 
Mladen Gogala
Oracle DBA
Ext. 121
</pre>
</body>
</html>

--------------050507050508070101070401--

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