Re: Too many cursors

From: Stax <stax0sh_at_my-deja.com>
Date: Thu, 30 Nov 2000 10:16:50 GMT
Message-ID: <9059eg$n3b$1@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 - 04:16:50 CST