How to work with SQL*Loader and trigger?(b20)

I've got a strange problem.
I'm trying to load data via the SQL*Loader into my DB. The table I'm
inserting in has a before row trigger, that writes something into an
other table. The problem I'm running in right now is, that the Loader
gets a lot of Errormessages from the trigger. (the mutating table one.)
The strange thing is, that I get this message for every second row I'm
trying to insert.
If I'm deleting the first row in the list of inserts, I experince the
same thing, every second row is "wrong".
If I insert the rows one by one manually everything works fine.

So my assumption is, that the loader is too fast, and the trigger is not
finished with doing it's work before the next row tries to insert. Is
that possible? Or does anyone know a workaround?


Ans:

Lets say you have the following:

drop table t;

create table t ( x int  );

create or replace trigger t_trigger
before insert on t
for each row
declare
    l_cnt   number;
begin
    select count(*) into L_cnt from t where x = :new.x;
end;
/
 

If you insert 1 row, you'll succeed as follows:

SQL> insert into t values ( 1 );

1 row created.
 

But if you insert >1 row, if fails:

SQL> insert into t select 1 from dual union all select 1 from dual;
insert into t select 1 from dual union all select 1 from dual
            *
ERROR at line 1:
ORA-04091: table TKYTE.T is mutating, trigger/function may not see it
ORA-06512: at "TKYTE.T_TRIGGER", line 4
ORA-04088: error during execution of trigger 'TKYTE.T_TRIGGER'
 

This is the documented behaviour of INSERT/FOR EACH ROW triggers (in the
applications developers guide, you can read about it).

So, how to work around with sqlldr?  If I take a control file like:
 

LOAD DATA
INFILE *
INTO TABLE T
REPLACE
FIELDS TERMINATED BY ',' OPTIONALLY ENCLOSED BY '"'
(X)
BEGINDATA
1
1
1
1
1
1
-------------- eof -------------

and just run:

$ sqlldr tkyte/tkyte x.ctl

I'll get:

Record 2: Rejected - Error on table T.
ORA-04091: table TKYTE.T is mutating, trigger/function may not see it
ORA-06512: at "TKYTE.T_TRIGGER", line 4
ORA-04088: error during execution of trigger 'TKYTE.T_TRIGGER'

Record 4: Rejected - Error on table T.
ORA-04091: table TKYTE.T is mutating, trigger/function may not see it
ORA-06512: at "TKYTE.T_TRIGGER", line 4
ORA-04088: error during execution of trigger 'TKYTE.T_TRIGGER'

Record 6: Rejected - Error on table T.
ORA-04091: table TKYTE.T is mutating, trigger/function may not see it
ORA-06512: at "TKYTE.T_TRIGGER", line 4
ORA-04088: error during execution of trigger 'TKYTE.T_TRIGGER'

Table T:
  3 Rows successfully loaded.
  3 Rows not loaded due to data errors.
  0 Rows not loaded because all WHEN clauses were failed.
  0 Rows not loaded because all fields were null.

in my log file (every other record failed).  If instead I execute:

$ sqlldr tkyte/tkyte x.ctl rows=1

Table T:
  6 Rows successfully loaded.
  0 Rows not loaded due to data errors.
  0 Rows not loaded because all WHEN clauses were failed.
  0 Rows not loaded because all fields were null.

I get them all loaded.  sqlldr batches them up and inserts >1 per statement --
that causes the mutating trigger error.  If sqlldr loads 1 row at a time -- no
error.

hope this helps...

Thomas Kyte
[email protected]
Oracle Service Industries
Reston, VA   USA
 

Hosted by www.Geocities.ws

1