Re: Dynamic PL/SQL in proc called from trigger

Thank you to the one person who replied, you know who you are :-)

  Can anyone else help???

-- 
--Paul
---------|						      |---------
	  Legitimate replies must remove spam from my address.
---------|						      |---------


Paul Passarelli <paulp_at_spam.talkthru.com> wrote in article
<01bc9c6f$9632b620$be6964c7_at_kosh>...


> I guess I still have a bone in my head that make me think that when I

 issue


> a PL/SQL statement from C, I can assume that triggers are an implicit

> extension of the code. ( I suppose I've been writing DLL's for too long

:-}


> )

> 

>   Anyway, can anyone help me with a workaround to allow me to execute DDL

> from an AFTER UPDATE trigger?  I'm trying to issue a CREATE USER

 statement


> in a trigger, but moved it to a procedure where it's now like:

> 

>         /* Parse and immediately execute dynamic SQL statement built by

>             concatenating user name to CREATE USER command. */

>         dbms_sql.parse( cid, 

>         'CREATE USER ' || tUserName ||

>         ' IDENTIFIED BY ' || tPassword ||

>         ' DEFAULT TABLESPACE user_data' || 

>         ' TEMPORARY TABLESPACE temporary_data' ||

>         ' QUOTA UNLIMITED ON user_data' ||

>         ' QUOTA UNLIMITED ON temporary_data', 

>         dbms_sql.v7 ) ;

> 

> but this generates an exception ORA-04092 cannot COMMIT in trigger,

 because


> DDL has an implicit commit, AFAIK.

> 

>   Every time this comes up, I have to go back, check out a whole

> mess-o-code, make 1 line additions to a PL/SQL block, and re-test the

 whole


> damn project.

> 

> PLEASE, PLEASE, PLEASE!  There has got to be a better way?

> 

> -- 

> --Paul

> ---------|						      |---------

> 	  Legitimate replies must remove spam from my address.

> ---------|						      |---------

> 

>