Oracle does not allow a commit (or rollback or savepoint)in a trigger. Your procedure must have an implicit commit because of the use of DDL. This is allowed in SLQ obviously but explains why it fails when called from a trigger.
What exactly is the contents of the procedure. Can you work around it?
John
-- --Original Message-- -- From: Kean Jacinta [mailto:jacintakean@(protected)] Sent: 29 June 2005 11:22 To: Hallas, John, Tech Dev; oracle-l@(protected) Subject: RE: EXEC pROCEDURE IN TRIGGER
> What failures message do you get JK > > It is possible and here is part of an old post that > shows it in use > > --create_LOGON_MULTIPLE_CHECK.sql > CREATE OR REPLACE TRIGGER LOGON_MULTIPLE_CHECK > AFTER logon ON DATABASE > DECLARE > client_info_str V$SESSION.CLIENT_INFO%TYPE; > var_username V$SESSION.USERNAME%TYPE := null; > kill_Login EXCEPTION; > PRAGMA EXCEPTION_INIT( kill_Login, -20997 ); begin > -- Set information string to uniquely identify this > session > client_info_str := 'Logon_Trigger_' || > LTRIM(dbms_random.value,'.'); > -- Push information string into v$session > > DBMS_APPLICATION_INFO.SET_CLIENT_INFO(client_info_str); > > -- --Original Message-- -- > From: oracle-l-bounce@(protected) > [mailto:oracle-l-bounce@(protected)] On Behalf Of > Kean Jacinta > Sent: 29 June 2005 09:52 > To: oracle-l@(protected) > Subject: EXEC pROCEDURE IN TRIGGER > > Hi, > > Would it be possible to execute my procedure in > trigger ? > > I have created a test procedure name : testproc > > I can execute this testproc in sql/plus by issue > this > command > > sql:> exec testproc('abc'); -- no problem > > I thought of calling this from trigger body, is that > possible ? > > example in trigger abc_trg > > Begin > exec testproc('abc'); > end; > > I tried ... got error. Wonder if this is possible. > > Thank in Advanced > > Cheers > JK > > > > __ ____ ____ ____ ____ ____ ____ ____ ____ ____ ____ > > Yahoo! Sports > Rekindle the Rivalries. Sign up for Fantasy Football > > http://football.fantasysports.yahoo.com > -- > http://www.freelists.org/webpage/oracle-l > > >
__ ____ ____ ____ ____ ____ ____ ____ ____ ____ __ Do You Yahoo!? Tired of spam? Yahoo! Mail has the best spam protection around http://mail.yahoo.com
