Re: Are PL/SQL tables transaction safe?

From: Thomas Kyte <tkyte_at_us.oracle.com>
Date: Thu, 21 May 1998 16:19:59 GMT
Message-ID: <356c535b.14743399@192.86.155.100>

A copy of this was sent to "Connect Computer" <john.mara_at_connects.com> (if that email address didn't require changing) On 21 May 1998 15:51:19 GMT, you wrote:

>I am writing triggers to update history tables and have encountered the
>dreaded 'mutating table' error. To get around that, I have created a
>package with variables, a row trigger to save the key and a statement
>trigger to affect the changes.
>
>My question is about the PL/SQL package variables. I am using a counter to
>index the variables. At the first execution of the row trigger, I set it to
>1. If multiple rows are affected, I increment the counter. In the statement
>trigger, I loop through, do my work and reset the index to 0.
>
>Is this safe within a transaction? Can these package variables be accessed
>concurrently by more than one trigger? The only code against these
>variables is in the trigger.
>
>I have scoured the Oracle doco and haven't come up with any definitive
>explanation. Can anyone help me answer this?
>
>Thanks.

Pl/sql variables in packages are SESSION 'safe'. Oracle will share the code amongst everyone in the database (shared sql does that) but each process will have its own 'data segment' if you will in the database. Each package has its own state and that state is not visible to other sessions.

In order to do what you want however, you need three triggers -- BEFORE, AFTER FOR EACH ROW, and AFTER.

The BEFORE should reset the package state (especially the counter). The AFTER FOR EACH ROW will set the state and increment the counter. The AFTER will process the data and optionally reset the state.

The reason you need three is because you might have an error that occurrs AFTER your row trigger fires at least once but BEFORE (or during) your After trigger. That would leave the package in an unknown state and the next update to that table would get whatever happened to be left in it.

Here is an example:

create or replace package emp_pkg
as

    type ridArray is table of rowid index by binary_integer;     rids ridArray;
    cnt number;
end;
/  

create or replace trigger emp_bu
before update on emp
begin

    emp_pkg.cnt := 0;
end;
/  

create or replace trigger emp_aufer
after update on emp
for each row
begin

    emp_pkg.cnt := emp_pkg.cnt + 1;
    emp_pkg.rids( emp_pkg.cnt ) := :new.rowid; end;
/  

create or replace trigger emp_au
after update on emp
declare

    avg_sal number;
begin

    for i in 1 .. emp_pkg.cnt loop

        select avg(sal) into avg_sal
          from emp
         where deptno = ( select deptno
                            from emp
                           where rowid = emp_pkg.rids(i) );
 
        -- do something about it.....
        dbms_output.put_line( emp_pkg.rids(i) || ' avg = ' || avg_sal );

    end loop;
end;
/  

update emp set ename = upper(ename);  

Thomas Kyte
tkyte_at_us.oracle.com
Oracle Government
Herndon VA  

http://govt.us.oracle.com/ -- downloadable utilities  

---

Opinions are mine and do not necessarily reflect those of Oracle Corporation  

Anti-Anti Spam Msg: if you want an answer emailed to you, you have to make it easy to get email to you. Any bounced email will be treated the same way i treat SPAM-- I delete it. Received on Thu May 21 1998 - 11:19:59 CDT