| Oracle FAQ | Your Portal to the Oracle Knowledge Grid | |
 |
 | |||
| Oracle FAQ | Your Portal to the Oracle Knowledge Grid | |
|
| |||
Home -> Community -> Usenet -> c.d.o.misc -> Re: Dynamic PL/SQL in proc called from trigger
Triggers are an implicit extension of the code, they just can't commit.
anyway, what you can do is use dbms_job to schedule the creation of the user to happen 'really soon' after the transaction that inserts the row commits. Create a procedure to create the user and instead of calling it in the trigger, schedule it to run once right after the commit happens. The job submitted via dbms_job can commit, rollback, whatever.
Make sure you configure the various job_* init.ora parms, for example:
job_queue_interval integer 60 job_queue_processes integer 1
Checks the queue for work every 60 seconds, and uses one background thread to execute jobs.
On 29 Jul 1997 22:37:26 GMT, "Paul Passarelli" <paulp_at_spam.talkthru.com> wrote:
>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?
Thomas Kyte
tkyte_at_us.oracle.com
Oracle Government
Bethesda MD
http://govt.us.oracle.com/ -- downloadable utilities
 |
 |