Re: Too many cursors

"Van Messner" <vmessner_at_bestweb.net> wrote in message news:EegV5.32$5d.5152_at_newshog.newsread.com...
> 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 am not an oracle expert by any means I know alot more about Informix. I know that in Informix the trigger would open a transaction and when you tried to open another transaction inside the already transaction it would not let you do it. I assume it could be the same in Oracle. Received on Wed Nov 29 2000 - 20:54:26 CST