Oracle FAQ Your Portal to the Oracle Knowledge Grid
HOME | ASK QUESTION | ADD INFO | SEARCH | E-MAIL US
 

Home -> Community -> Usenet -> c.d.o.server -> Re: Too many cursors

Re: Too many cursors

From: Van Messner <vmessner_at_bestweb.net>
Date: Fri, 01 Dec 2000 01:07:31 GMT
Message-ID: <nRCV5.72$7d.5892@newshog.newsread.com>

Hi Stax:

    Many thanks. It looks like your solution, or some variation, will be necessary. I had hoped there was something a lot shorter, but it doesn't look like it.

Van

"Stax" <stax0sh_at_my-deja.com> wrote in message news:9059eg$n3b$1_at_nnrp1.deja.com...
> Hi
>
> Recursive call trigger LG_T_P
> from PROCEDURE CLEANUP
>
> Use flag for calculate call PROCEDURE CLEANUP
>
> CREATE OR REPLACE PACKAGE UPDATE_FLAG AS
> PAKAGE_VARIABLE_FLAG NUMBER;
> FUNCTION U_FLAG RETURN NUMBER;
> PRAGMA RESTRICT_REFERENCES(U_FLAG,WNDS);
> END;
> /
>
> CREATE OR REPLACE PACKAGE BODY UPDATE_FLAG AS
> FUNCTION U_FLAG RETURN NUMBER AS
> BEGIN
> PAKAGE_VARIABLE_FLAG:=PAKAGE_VARIABLE_FLAG+1;
>
> IF PAKAGE_VARIABLE_FLAG>3 THEN
> PAKAGE_VARIABLE_FLAG:=0;
> END IF;
> --dbms_output.put_line(to_char(PAKAGE_VARIABLE_FLAG));
> RETURN PAKAGE_VARIABLE_FLAG;
> END;
> BEGIN
> PAKAGE_VARIABLE_FLAG:=0;
> END;
> /
>
> CREATE OR REPLACE PROCEDURE CLEANUP
> IS
> BEGIN
> IF UPDATE_FLAG.U_FLAG=2 then
>
> 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 IF;
> END CLEANUP;
> /
> CREATE OR REPLACE TRIGGER LG_T_FLAG
> BEFORE UPDATE ON PERSONS_MSTR
> ^^^^^^
> BEGIN
> IF UPDATE_FLAG.U_FLAG=0 then
> NULL;
> END IF;
> END LG_T_FLAG;
> /
>
>
> In article <EegV5.32$5d.5152_at_newshog.newsread.com>,
> "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
> >
> >
> --
> Stax
>
>
>
> Sent via Deja.com http://www.deja.com/
> Before you buy.
Received on Thu Nov 30 2000 - 19:07:31 CST

Original text of this message

HOME | ASK QUESTION | ADD INFO | SEARCH | E-MAIL US