logoff trigger

From: <becker.bill_at_marshfieldclinic.org>
Date: Mon, 21 Oct 2002 13:03:27 -0800
Message-ID: <F001.004EF0FD.20021021130327@fatcity.com>

Hello,

We are on 8.1.7.4.0 (on Solaris), and are having a problem with a logoff trigger and "ORA-00164: autonomous transaction disallowed within distributed transaction". The logoff trigger is defined as: CREATE OR REPLACE TRIGGER get_logoff_stats_trg BEFORE LOGOFF ON DATABASE
BEGIN
capture_session_stats_prc;
END; The purpose of the procedure called by the trigger is to capture session stats and commit them to a table, so the procedure is defined as an autonomous transaction; removing the autonomous transaction pragma results in:

ORA-00604: error occurred at recursive SQL level 1
ORA-04092: cannot ROLLBACK in a trigger
ORA-06512: at "CAPTURE_SESSION_STATS_PRC", line 116
ORA-04092: cannot COMMIT in a trigger

Most of our users only encounter this error when executing some SELECT which involves a reference to a database link, which apparently qualifies as a distributed transaction. Anyway, the SELECT executes successfully, but we fail to capture their session stats upon exit.

The suggested oerr action for ora-164 is to rollback or commit before calling the autonomous transaction pragma; but Oracle does not allow a rollback or commit in the trigger which calls the autonomous transaction procedure.

An exception handler doesn't seem to catch an ora-00164 error. (Anyone know why? I thought all ORA- errors could be captured by exception handlers.)

ORA-00164 is fixed in Oracle9i (actually, Oracle says this is not a bug, but a feature that was changed, not fixed), but we can't upgrade to 9i because some of our third-party tools are not yet compatible.

The only thing I can think of is to ask users to do a commit or rollback in their sessions just before exiting if they referenced a db link. I know this isn't practical. Does anyone have a better suggestion?

As always, many thanx to any responders.

--

Please see the official ORACLE-L FAQ: http://www.orafaq.com
--

Author:
  INET: becker.bill_at_marshfieldclinic.org

Fat City Network Services    -- 858-538-5051 http://www.fatcity.com
San Diego, California        -- Mailing list and web hosting services
---------------------------------------------------------------------

To REMOVE yourself from this mailing list, send an E-Mail message to: ListGuru_at_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). Received on Mon Oct 21 2002 - 16:03:27 CDT