Message-Id: <10507.106585@fatcity.com> From: "Veera Prasad" Date: Wed, 24 May 2000 16:20:12 -0400 Subject: Re: Executing DDL from trigger --------------BB01CBF3CE51A0F2C975831C Content-Type: text/plain; charset=us-ascii Content-Transfer-Encoding: 7bit Using autonomous_Transaction in trigger works for DDL's too. check it out VPRASAD: PRECY>create or replace trigger test_table 2 after insert on emp 3 declare 4 pragma autonomous_transaction; 5 begin 6 execute immediate 'create table test1 (no number)'; 7 end; 8 / Trigger created. VPRASAD: PRECY>insert into emp (empno) values(9999); 1 row created. VPRASAD: PRECY>desc test1 Name Null? Type ----------------------------------------------------- -------- ------------------------------------ NO NUMBER I take back my statement made in my earlier mail like " autonomous transaction doesn't seems like working for DDL's " which is not really true as it proved above. Jim Conboy, I think u got the solution for your problem. Regards Veera Prasad Jim Conboy wrote: > Hi folks- > > I'm trying to simplify the process of creating a user account for a COTS package where I can't touch the code. There's some user configuration within the application, and of course there's creating the actual Oracle account. I created an insert trigger for the main user configuration table and used DBMS_SQL to execute a CREATE USER statement. The code works fine as a stored procedure, but as a trigger I get an error 4092 that commit not allowed in a trigger, presumably because of the CREATE USER DDL. I tried having the trigger execute the procedure but got the same error - actually I'd be disturbed if it didn't! I'm not looking for anything too involved here because a script will do the trick fine, just wondering if anybody has a simple suggestion to make this work. Thanks. > > Jim > > -- > Author: Jim Conboy > INET: Jim.Conboy@trw.com > > Fat City Network Services -- (858) 538-5051 FAX: (858) 538-5051 > San Diego, California -- Public Internet access / Mailing Lists > -------------------------------------------------------------------- > To REMOVE yourself from this mailing list, send an E-Mail message > to: ListGuru@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). --------------BB01CBF3CE51A0F2C975831C Content-Type: text/html; charset=us-ascii Content-Transfer-Encoding: 7bit Using autonomous_Transaction in trigger works for DDL's too.
check it out

VPRASAD: PRECY>create or replace trigger test_table
  2  after insert on emp
  3  declare
  4  pragma autonomous_transaction;
  5  begin
  6  execute immediate 'create table test1 (no number)';
  7  end;
  8  /

Trigger created.

VPRASAD: PRECY>insert into emp (empno) values(9999);

1 row created.

VPRASAD: PRECY>desc test1
 Name                                                  Null?    Type
 ----------------------------------------------------- -------- ------------------------------------
 NO                                                             NUMBER
 

I take back my statement made in my earlier mail like " autonomous transaction doesn't
seems like working for DDL's " which is not really true as it proved above.

Jim Conboy, I think u got the solution for your problem.

Regards
Veera Prasad
 

Jim Conboy wrote:

Hi folks-

I'm trying to simplify the process of creating a user account for a COTS package where I can't touch the code.  There's some user configuration within the application, and of course there's creating the actual Oracle account.  I created an insert trigger for the main user configuration table and used DBMS_SQL to execute a CREATE USER statement.  The code works fine as a stored procedure, but as a trigger I get an error 4092 that commit not allowed in a trigger, presumably because of the CREATE USER DDL.  I tried having the trigger execute the procedure but got the same error - actually I'd be disturbed if it didn't!  I'm not looking for anything too involved here because a script will do the trick fine, just wondering if anybody has a simple suggestion to make this work.  Thanks.

Jim

--
Author: Jim Conboy
  INET: Jim.Conboy@trw.com

Fat City Network Services    -- (858) 538-5051  FAX: (858) 538-5051
San Diego, California        -- Public Internet access / Mailing Lists
--------------------------------------------------------------------
To REMOVE yourself from this mailing list, send an E-Mail message
to: ListGuru@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).