Re: sequence #'s and triggers

From: Kenny Gump <kgump_at_mylanlabs.com>
Date: Wed, 28 Apr 1999 10:04:10 -0400
Message-ID: <37271501.0@news.mountain.net>

The problem is the 2nd SELECT INTO is not returning data and is raising the NO_DATA_FOUND exception. Try the following:

DECLARE
    new_value NUMBER;
    test_value NUMBER;
    dummy varchar2(1);

cursor my_cursor is
  SELECT 'x'
  FROM analyst
  WHERE analyst.analyst_id = new_value;

BEGIN
    SELECT analyst_id_seq.nextval INTO new_value FROM dual;

    open cursor my_cursor;
    fetch my_cursor into dummy;

    WHILE( my_cursor%FOUND ) LOOP

        SELECT analyst_id_seq.nextval INTO new_value FROM dual;
         fetch my_cursor into dummy;
     END LOOP;

    close cursor my_cursor;

   :new.analyst_id := new_value;
END analyst_id_create;

Kenny Gump
OCP 7.3 DBA Mel Haynes Jr wrote in message <925245976.658.18_at_news.remarQ.com>...
>I am creating generated numbers for a field in a table by calling a trigger
>that gets the nexval in a sequence. I have added code that checks the
number
>first to see if it exists in the table and if it does, it grabs another
>number from the sequence object. When the trigger is fired, I am getting an
>error saying "no data found". Since I am a newbie to Oracle, can someone
>tell me what I am doing wrong? Here is the trigger body. Thanks
>
>DECLARE
> new_value NUMBER;
> test_value NUMBER;
>BEGIN
> SELECT analyst_id_seq.nextval INTO new_value FROM dual;
> SELECT analyst_id INTO test_value FROM analyst WHERE analyst.analyst_id
>= new_value;
>
> WHILE( SQL%FOUND ) LOOP
> SELECT analyst_id_seq.nextval INTO new_value FROM dual;
> SELECT analyst_id INTO test_value FROM analyst WHERE
> analyst.analyst_id = new_value;
> END LOOP;
>
> :new.analyst_id := new_value;
>
>END analyst_id_create;
>
>--
>Mel Haynes Jr
>mhaynesjr_at_pcisys.net
>www.pcisys.net/~mhaynesjr
>
>"Trust the computer industry to shorten 'Year 2000' to 'Y2K.' It was that
>kind of thinking that caused the problem in the first place."
>
>- anonymous net wisdom
>
>
Received on Wed Apr 28 1999 - 09:04:10 CDT