Re: Any way to catch end of transaction programmatically?

On Fri, 16 Feb 2001, Eskov Anton wrote:

> Hi
> Is there any way to get notified when COMMIT/ROLLBACK statement is
> executed?
>
> SY
> Anton

Here's an example. I'll leave making use of this as an exercise for the reader. ;)

Jared

declare

        cursor c_trans_count ( statname_in  varchar2 )
        is
        select value
        from v$sysstat
        where name = statname_in;

        v_commit_count_old pls_integer;
        v_commit_count_new pls_integer;
        v_rollback_count_old pls_integer;
        v_rollback_count_new pls_integer;

        v_commit_name varchar2(30) := 'user commits';
        v_rollback_name varchar2(30) := 'user rollbacks';

begin

        open c_trans_count(v_commit_name);
        fetch c_trans_count into v_commit_count_new;
        close c_trans_count;
        v_commit_count_old := v_commit_count_new;

        open c_trans_count(v_rollback_name);
        fetch c_trans_count into v_rollback_count_new;
        close c_trans_count;
        v_rollback_count_old := v_rollback_count_new;

        for x in 1..10
        loop

                open c_trans_count(v_commit_name);
                fetch c_trans_count into v_commit_count_new;
                close c_trans_count;

                open c_trans_count(v_rollback_name);
                fetch c_trans_count into v_rollback_count_new;
                close c_trans_count;

                if v_commit_count_old != v_commit_count_new
                then
                        dbms_output.put_line('user commit at ' || 
to_char(sysdate,'mm/dd/yyyy hh24:mi:ss'));
                end if;

                if v_rollback_count_old != v_rollback_count_new
                then
                        dbms_output.put_line('user rollback at ' || 
to_char(sysdate,'mm/dd/yyyy hh24:mi:ss'));
                end if;

                v_commit_count_old := v_commit_count_new;
                v_rollback_count_old := v_rollback_count_new;

                dbms_lock.sleep(1);


        end loop;

end;

/

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

Fat City Network Services    -- (858) 538-5051  FAX: (858) 538-5051
San Diego, California        -- Public Internet access / Mailing Lists
--------------------------------------------------------------------
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).