Paul Passarelli wrote:
>
> 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.
> > ---------| |---------
> >
> >
Assuming you are in a client - server environment, you can
use dbms_pipe (and I think there is a dbms_signal or something
similar) to coordinate action between a continuously running
server side job, say , one that could effectively create a
user, and a trigger that signals that server job to do the
dirty deed. Try a newsgroup search on dbms_pipe - there
is some current discussion going on and some folks that are
up to speed on it. You should be able to get what you need
in short order. Unfortunately I don't have specifics cause
it's been a while and my retention seems to be getting worse
the older I get and ... what was it I was talking about?
Anyway,
Good luck
TRG
Received on Thu Jul 31 1997 - 00:00:00 CDT
