Oracle FAQ Your Portal to the Oracle Knowledge Grid
HOME | ASK QUESTION | ADD INFO | SEARCH | E-MAIL US
 

Home -> Community -> Usenet -> c.d.o.server -> Re: Too many cursors

Re: Too many cursors

From: Mike Krolewski <mkrolewski_at_rosetta.org>
Date: Thu, 30 Nov 2000 02:18:14 GMT
Message-ID: <904dd3$24i$1@nnrp1.deja.com>

In article <EegV5.32$5d.5152_at_newshog.newsread.com>,   "Van Messner" <vmessner_at_bestweb.net> wrote:
> I have a persons table which is self-referential. Persons may have a
> supervisor. When I mark a person as inactive, I want to set the
 supervisor
> column to null for any person who had the inactive one as his
 supervisor.
>
> The following procedure works and does what I want. I can execute it
 in
> SQL*Plus. Also I can just pull out the SQL and it runs fine.
>
> CREATE OR REPLACE PROCEDURE CLEANUP
> IS
> BEGIN
> UPDATE PERSONS_MSTR
> SET person_supervisor = null
> WHERE person_supervisor in (
> SELECT person_supervisor
> FROM PERSONS_MSTR
> WHERE sysdate - person_date_made_inactive < 1
> );
> END CLEANUP;
> /
>
> So I thought I'd use an after update statement trigger to execute the
> procedure, since I thought that was legal.
>
> CREATE OR REPLACE TRIGGER LG_T_P
> AFTER UPDATE ON PERSONS_MSTR
> BEGIN
> CLEANUP;
> END LG_T_P
>
> But this generates an ORA-01000 too many cursors error.
>
> Any suggestions, remedies or clues?
>
> Thanks,
>
> Van
>
>

The problem is the you have done a classic no-no. Your after-update trigger updates the same table. As each row is updated, the afterupdate  trigger is issued, which causes more updates and more triggers, etc.

The obvious solution is to move the 'cleanup' to be outside of the trigger. You could write a cron job to perform the task or use dbms_job to run this update. Once a day should be sufficient. If you need it more often -- set it up for every n minutes. This work especially well if you want to wait ( 1 day ) after setting the table. If no updates had occurred after the date was set, the trigger will not fire. With the external program, the action will occur with or without other updates.

Solution 2 is to make the update more restrictive. It apears that you only need to clean up the table when the inactive date is set for a supervisor. Rather than check all records every time any record is changed, change the record when a supervisor's field is changed. Updates triggers can be activated on a particular field, set of fields , or condition in that field(s)

--
Michael Krolewski
Rosetta Inpharmatics
mkrolewski_at_rosetta.org
              Ususual disclaimers


Sent via Deja.com http://www.deja.com/
Before you buy.
Received on Wed Nov 29 2000 - 20:18:14 CST

Original text of this message

HOME | ASK QUESTION | ADD INFO | SEARCH | E-MAIL US