Re: Mutating table problem + open cursors

Henri van den Bulk wrote:

> We have been having problems with mutating table errors. The senario is
> that we have a table
> that contains parent-child relatied rows. For example row 1 can be the
> parent for row 3. The entity is such that we need this relationship back
> to the same entity.
>
> To inforce RI in the db we create a trigger that on delete of row would
> delete all the child rows in the same entity. Of course this cause a
> mutating table error.
> Now I found an article that advised to write a trigger that records the
> fact that a row
> has been deleted (after delete for each row trigger). This trigger
> writes the the SK into a temp. table. Then after the delete (after
> delete on entiry trigger) an other trigger fires and deletes the child
> rows that are assosiated to the rows that have been recorded in the
> temp. table.
>
> The problem that we're seeing now is that the db. generates an error
> stating that it has exceeded the max open cursors "ORA-01000: maximum
> open cursors exceeded". This is
> weird because it is set to 150 in init.ora and there are only 6 rows in
> the database.

  I think you have written some infinite recusive triggers. As a result, it is infinite looping until all the cursors are used up. Anyway, in your case, you can use ON DELETE CASCADE integrity constraints.

---
Name   : Lun Wing San
Title  : Oracle Application Developer of Hong Kong Productivity Council
         Oracle Database Administrator and System Administrator of QRC
Phone  : (852)27885841

This posting represents the personal opinions of the author. It is not the
official opinion or policy of the author's employer. Warranty expired when you
opened this article and I will not be responsible for its contents or use.