Home » SQL & PL/SQL » SQL & PL/SQL » maximum number of recursive SQL levels (50) exceeded (toad 8.1)
maximum number of recursive SQL levels (50) exceeded [message #303367] Fri, 29 February 2008 01:09 Go to next message
ram anand
Messages: 244
Registered: February 2008
Location: india
Senior Member
Hi guys,
I have created a trigger
CREATE OR REPLACE TRIGGER TRG_CUSTOMER_GROUP_I
BEFORE INSERT
ON CUSTOMER_GROUP
FOR EACH ROW
DECLARE
V_COUNT NUMBER(9);
BEGIN
SELECT COUNT(*)
INTO V_COUNT
FROM CUSTOMER
WHERE CUSTOMER_ID =:NEW.CUSTOMER_GROUP_ID;
IF (V_COUNT = 0) THEN
RAISE_APPLICATION_ERROR(-20100,'error occured (CUSTOMER.CUSTOMER_IDDOESNOTEXISTS)');
ELSE
INSERT INTO CUSTOMER_GROUP(CUSTOMER_GROUP_ID,CUSTOMER_GROUP_NAME)
VALUES (:NEW.CUSTOMER_GROUP_ID,:NEW.CUSTOMER_GROUP_NAME);

END IF;
END;
/
INSERT INTO CUSTOMER_GROUP ( CUSTOMER_GROUP_ID, CUSTOMER_GROUP_NAME)
VALUES ( 'sample', 'sample');
/

while I insert a record into it the below error throws.so can any one give me a proper explaination for the below error mentioned,pls
ORA-00036: maximum number of recursive SQL levels (50) exceeded
ORA-06512: at "OPSDEV.TRG_CUSTOMER_GROUP_I", line 4
ORA-04088: error during execution of trigger 'OPSDEV.TRG_CUSTOMER_GROUP_I'
ORA-06512: at "OPSDEV.TRG_CUSTOMER_GROUP_I", line 11
ORA-04088: error during execution of trigger 'OPSDEV.TRG_CUSTOMER_GROUP_I'
ORA-06512: at "OPSDEV.TRG_CUSTOMER_GROUP_I", line 11
ORA-04088: error during execution of trigger 'OPSDEV.TRG_CUSTOMER_GROUP_I'
ORA-06512: at "OPSDEV.TRG_CUSTOMER_GROUP_I", line 11
ORA-04088: error during execution of trigger 'OPSDEV.TRG_CUSTOMER_GROUP_I'
ORA-06512: at "OPSDEV.TRG_CUSTOMER_GROUP_I", line 11
ORA-04088: error during execution of trigger 'OPSDEV.TRG_CUSTOMER_GROUP_I'
ORA-06512: at "OPSDEV.TRG_CUSTOMER_GROUP_I", line 11
ORA-04088: error during execution of trigger 'OPSDEV.TRG_CUSTOMER_GROUP_I'
ORA-06512: at "OPSDEV.TRG_CUSTOMER_GROUP_I", line 11
ORA-04088: error during execution of trigger 'OPSDEV.TRG_CUSTOMER_GROUP_I'
ORA-06512: at "OPSDEV.TRG_CUSTOMER_GROUP_I", line 11
ORA-04088:

Thanks,

[Updated on: Fri, 29 February 2008 01:14]

Report message to a moderator

Re: maximum number of recursive SQL levels (50) exceeded [message #303374 is a reply to message #303367] Fri, 29 February 2008 01:21 Go to previous messageGo to next message
Michel Cadot
Messages: 68718
Registered: March 2007
Location: Saint-Maur, France, https...
Senior Member
Account Moderator
ORA-00036: "maximum number of recursive SQL levels (%s) exceeded"
 *Cause:  An attempt was made to go more than the specified number
          of recursive SQL levels.
 *Action: Remove the recursive SQL, possibly a recursive trigger

Read your code.
You insert into CUSTOMER_GROUP that triggers TRG_CUSTOMER_GROUP_I that inserts into CUSTOMER_GROUP that triggers TRG_CUSTOMER_GROUP_I that inserts into CUSTOMER_GROUP that triggers TRG_CUSTOMER_GROUP_I that inserts into CUSTOMER_GROUP that triggers TRG_CUSTOMER_GROUP_I that inserts into CUSTOMER_GROUP that triggers TRG_CUSTOMER_GROUP_I that inserts into CUSTOMER_GROUP that triggers TRG_CUSTOMER_GROUP_I that inserts into CUSTOMER_GROUP that triggers TRG_CUSTOMER_GROUP_I that inserts into CUSTOMER_GROUP that triggers TRG_CUSTOMER_GROUP_I that inserts into CUSTOMER_GROUP that triggers TRG_CUSTOMER_GROUP_I that inserts into CUSTOMER_GROUP that triggers TRG_CUSTOMER_GROUP_I that inserts into CUSTOMER_GROUP that triggers TRG_CUSTOMER_GROUP_I that inserts into CUSTOMER_GROUP that triggers TRG_CUSTOMER_GROUP_I that inserts into CUSTOMER_GROUP that triggers TRG_CUSTOMER_GROUP_I that inserts into CUSTOMER_GROUP that triggers TRG_CUSTOMER_GROUP_I that inserts into CUSTOMER_GROUP that triggers TRG_CUSTOMER_GROUP_I that inserts into CUSTOMER_GROUP that triggers TRG_CUSTOMER_GROUP_I that inserts into CUSTOMER_GROUP...

Do you see what I mean?

Regards
Michel
Re: maximum number of recursive SQL levels (50) exceeded [message #303381 is a reply to message #303374] Fri, 29 February 2008 01:41 Go to previous messageGo to next message
ram anand
Messages: 244
Registered: February 2008
Location: india
Senior Member
Hi,
I cannot able to understand what u say,pls with brief answer
Re: maximum number of recursive SQL levels (50) exceeded [message #303383 is a reply to message #303381] Fri, 29 February 2008 01:56 Go to previous messageGo to next message
MarcS
Messages: 312
Registered: March 2007
Location: Antwerp
Senior Member
In pseudo-code you have:

STEP-1 - INSERT INTO CUSTOMER_GROUP, continue with STEP-2
STEP-2 - FIRE TRIGGER TRG_CUSTOMER_GROUP_I, continue with STEP-3
STEP-3 - IF (V_COUNT != 0) THEN GO TO STEP-1

This is what we call a recursive/endless loop
Re: maximum number of recursive SQL levels (50) exceeded [message #303390 is a reply to message #303383] Fri, 29 February 2008 02:38 Go to previous messageGo to next message
ram anand
Messages: 244
Registered: February 2008
Location: india
Senior Member
Hi,
Thank you for your kind reply

Thanks,
Re: maximum number of recursive SQL levels (50) exceeded [message #303394 is a reply to message #303383] Fri, 29 February 2008 02:52 Go to previous messageGo to next message
ram anand
Messages: 244
Registered: February 2008
Location: india
Senior Member
Hi,
Can u give me a correct solution for this
Thanks,
Re: maximum number of recursive SQL levels (50) exceeded [message #303400 is a reply to message #303394] Fri, 29 February 2008 03:20 Go to previous messageGo to next message
Michel Cadot
Messages: 68718
Registered: March 2007
Location: Saint-Maur, France, https...
Senior Member
Account Moderator
Quote:
Can u give me a correct solution for this

Use a foreign key constraint.

Regards
Michel
Re: maximum number of recursive SQL levels (50) exceeded [message #303403 is a reply to message #303367] Fri, 29 February 2008 03:36 Go to previous messageGo to next message
MarcS
Messages: 312
Registered: March 2007
Location: Antwerp
Senior Member
If you really want to stick with the trigger then this would be a more valid approach:

CREATE OR REPLACE TRIGGER TRG_CUSTOMER_GROUP_I
BEFORE INSERT 
ON CUSTOMER_GROUP
FOR EACH ROW
DECLARE 
  V_COUNT NUMBER(9);	
BEGIN
  SELECT COUNT(*)
  INTO V_COUNT
  FROM CUSTOMER
  WHERE CUSTOMER_ID =:NEW.CUSTOMER_GROUP_ID;
  IF (V_COUNT = 0) THEN
   	  RAISE_APPLICATION_ERROR(-20100,'error occured
            (CUSTOMER.CUSTOMER_IDDOESNOTEXISTS)'); 
  END IF;
END;
/


Because when the trigger doesn't fail, the insert would be performed, you don't have to do it yourself.

But the solution provided by Michel is more appropriate: a foreign key between CUSTOMER and CUSTOMER_GROUP
Re: maximum number of recursive SQL levels (50) exceeded [message #303406 is a reply to message #303403] Fri, 29 February 2008 04:13 Go to previous message
ram anand
Messages: 244
Registered: February 2008
Location: india
Senior Member
Hi,
Thanks a lot
Previous Topic: external tables - which view to query?
Next Topic: Comments for external tables
Goto Forum:
  


Current Time: Thu Dec 05 00:14:35 CST 2024