Re: Too many cursors

From: Van Messner <vmessner_at_bestweb.net>
Date: Fri, 01 Dec 2000 01:05:57 GMT
Message-ID: <VPCV5.71$7d.6753@newshog.newsread.com>

Hi Adam:

    You are right. Now I need a solution.

Van

"adam" <ab_at_chaos-labs.com> wrote in message news:CjjV5.11378$GW5.73517_at_news-server.bigpond.net.au...
> "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 Thu Nov 30 2000 - 19:05:57 CST