Re: ORA-00036: maximum number of recursive SQL levels (50) exceeded

I wrote something about it in my limitations page http://www.itsystems.lv/gints/oracle_limits.htm

see No of cascading triggers = 32
Note1:
Someone said this number is 255. I could not find it in a short time in Oracle docs so I decided to test this limitation myself. I created similar tables table0..table300 with one number column and similar triggers that insert a value in the next table. And the result was... Error message ORA-00036: maximum number of recursive SQL levels (50) exceeded. So, as you can see the real number is 50. I tested it on 8.0.3, 8.1.6 and 8.1.7 with the same results. However in the real life I usually didn't need more than 2 or 3, so don't worry. And at last I found that it is O/S DEPENDENT, usually 32.

I'm rather sure it is connected with (not good) coding.

Gints Plivna
IT Sistēmas, Merķeļa 13, LV1050 Rīga
http://www.itsystems.lv/gints/

                                                                                                                                             
                      "Farnsworth, Dave"                                                                                                     
                      <DFarnsworth_at_Ashleyfur        To:       Multiple recipients of list ORACLE-L <ORACLE-L_at_fatcity.com>                    
                      niture.com>                   cc:                                                                                      
                      Sent by:                      Subject:  ORA-00036: maximum number of recursive SQL levels (50) exceeded                
                      root_at_fatcity.com                                                                                                       
                                                                                                                                             
                                                                                                                                             
                      2002.06.13 16:28                                                                                                       
                      Please respond to                                                                                                      
                      ORACLE-L                                                                                                               
                                                                                                                                             
                                                                                                                                             

I got this error in my <SID>Alert log yesterday. I found that this usually has to do with triggers that keep calling itself. Is this "recursive SQL level" an internal parameter that cannot be changed? I do not see this in my init.ora. Is this error due more to coding error?

Thanks,

Dave

--
Please see the official ORACLE-L FAQ: http://www.orafaq.com
--
Author: Farnsworth, Dave
  INET: DFarnsworth_at_Ashleyfurniture.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).




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

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).