| Oracle FAQ | Your Portal to the Oracle Knowledge Grid | |
 |
 | |||
| Oracle FAQ | Your Portal to the Oracle Knowledge Grid | |
|
| |||
Home -> Community -> Usenet -> c.d.o.server -> Re: Too many cursors
Hi Connor:
You're right. Stax, above has a solution and there is a similar one in the new O'Reilly Press book on PL/SQL. I had hoped for something shorter but I guess I'm out of luck.
Van
"Connor McDonald" <connor_mcdonald_at_yahoo.com> wrote in message
news:3A264156.2F97_at_yahoo.com...
> 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
>
>
 |
 |