Re: Too many cursors

In our last gripping episode "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
>
>

I would imagine that the open_cursors parameter in the init<SID>.ora file is set too low, probably at the default setting. Up it to 500 or so, that should correct the problem.

--
David Fitzjarrell
Oracle Certified DBA


Sent via Deja.com http://www.deja.com/
Before you buy.