Re: Too many cursors

Van Messner 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

I would say you're getting stuck in a recursive loop...Add a package variable to indicate if you're already in the trigger when clean-up is called...

HTH
COnnor

-- 
===========================================
Connor McDonald
http://www.oracledba.co.uk (mirrored at
http://www.oradba.freeserve.co.uk)

"Some days you're the pigeon, some days you're the statue"