Home » SQL & PL/SQL » SQL & PL/SQL » ORA-00036: maximum number of recursive SQL levels (50) exceeded
ORA-00036: maximum number of recursive SQL levels (50) exceeded [message #198360] Mon, 16 October 2006 15:53 Go to next message
TLegend33
Messages: 203
Registered: March 2005
Senior Member
I have a simple table:

SQL> desc manufacturer_dict
Name Null? Type
-------------------- -------- ----------------
MANUFACTURER_ID NOT NULL NUMBER(34,4)
MANUFACTURER_CODE VARCHAR2(10)
MANUFACTURER_DESC VARCHAR2(100)

I have a simple trigger:

create or replace trigger hippo.trmvx
before insert on manufacturer_dict
for each row
begin
insert into manufacturer_dict
(manufacturer_id)
values
(dict_seq.nextval);

end;
/

I have a simple control file for sqlldr:

load data
infile 'MVX.txt'
append into table manufacturer_dict
fields terminated by ";" optionally enclosed by '"'
(MANUFACTURER_CODE,
MANUFACTURER_DESC)

I am attempting to load MVX.txt using sqlldr. Before each row is loaded, I would like the trigger to populate the manufacturer_id field. I can't determine why I'm getting the below errors:

Record 1: Rejected - Error on table MANUFACTURER_DICT.
ORA-00036: maximum number of recursive SQL levels (50) exceeded
ORA-06512: at "HIPPO.TRMVX", line 2
ORA-04088: error during execution of trigger 'HIPPO.TRMVX'

What's recursive or incorrect with my trigger?

Thanks.
Re: ORA-00036: maximum number of recursive SQL levels (50) exceeded [message #198363 is a reply to message #198360] Mon, 16 October 2006 16:35 Go to previous messageGo to next message
Littlefoot
Messages: 20901
Registered: June 2005
Location: Croatia, Europe
Senior Member
Account Moderator
This is not the way it should be done; with the trigger you have, you'll - for each record inserted into the table - insert another record which will have only ID (and all other columns will be NULL). You'd, actually, have to UPDATE a record, not insert.

However, why wouldn't you use a sequence directly in the control file, during loading session? Simply use such a syntax with the column name:

MANUFACTURER_ID "sequence_name.NEXTVAL"

Or, use SQL*Loader built-in option to generate data: a sequence.
Re: ORA-00036: maximum number of recursive SQL levels (50) exceeded [message #198377 is a reply to message #198363] Mon, 16 October 2006 21:42 Go to previous messageGo to next message
rleishman
Messages: 3724
Registered: October 2005
Location: Melbourne, Australia
Senior Member
Hey, does anyone know why this didn't raise a Mutating Table error instead? It looks like it was actually trying to do the insert specified in the trigger...

Have we found a hole in the Mutating Table error?

Ross Leishman
Re: ORA-00036: maximum number of recursive SQL levels (50) exceeded [message #198389 is a reply to message #198377] Tue, 17 October 2006 00:31 Go to previous messageGo to next message
Frank
Messages: 7880
Registered: March 2000
Senior Member
Mutating table error normally does not occur during an insert of 1 row. Oracle knows exactly which row is mutating. See what Thomas Kyte has to say about it
(look for the "Why I can't get the 4091 error when insert?" follow-up)

(this doesn't make the OPs code less faulty, it's just a response to Ross' question)

[Updated on: Tue, 17 October 2006 00:32]

Report message to a moderator

Re: ORA-00036: maximum number of recursive SQL levels (50) exceeded [message #198527 is a reply to message #198389] Tue, 17 October 2006 09:06 Go to previous message
Ronald Beck
Messages: 121
Registered: February 2003
Senior Member
The table is not mutating. When he adds a row to the table, the trigger adds a row, which fires the trigger which adds a row, which fires the trigger, which adds a row... until you get a recursive error. The trigger should be...

create or replace trigger hippo.trmvx
before insert on manufacturer_dict
for each row
begin
  select dict_seq.nextval 
  into :new.manufacturer_id
  from dual;
end;
/



which is how I set most of my log tables that require a unique sequence for each row.

HTH,
Ron
Previous Topic: Calling a procedure inside a procedure [SOLVED]
Next Topic: Difference between Primary key and Unique key
Goto Forum:
  


Current Time: Sat Dec 10 11:10:50 CST 2016

Total time taken to generate the page: 0.12786 seconds