Search code examples
oraclecursorsequence

Oracle sequence and cursor

I am a SQL user and trying to understand the Oracle cursor code below .

I understand that the first block is getting the current sequence number from id_seq into id . BUT what is the second block doing ?

Particularly this line - IF c3%NOTFOUND OR c3_rec.id IS NULL THEN what is the meaning of c3 NOTFOUND and c3_rec.id IS NULL 

    CURSOR c3 IS
    SELECT id_seq.CURRVAL id
    FROM dual;

OPEN c3;
FETCH c3 INTO c3_rec;
IF c3%NOTFOUND OR c3_rec.id IS NULL THEN
Solution

  • Luke's comment is correct. The last line means that if no record is found, or if the value of column id is null then ... etc.

    In this particular case, I think that line is redundant. The cursor selects the CURRVAL of the sequence id_seq, which is the value most recently selected in the current session using NEXTVAL.

    Perhaps I'm missing something, but I only see these possibilities:

    1. If id_seq doesn't exist, an exception will be raised as soon as the cursor is declared.

    2. If id_seq exists, but a valid NEXTVAL has not been referenced in the current session, an exception will be raised as soon as the cursor is opened. (NEXTVAL won't be valid if it exceeds the sequence's MAXVALUE.)

    3. If a valid id_seq.NEXTVAL has been referenced in the current session, the CURRVAL will be the most recent valid value of NEXTVAL.

    If the code executes as far as the last line without falling over, then we already know that a record was found. c3_rec.id is the sum of two integers (the sequence's current value and its increment) so it cannot be null. Hence that line appears redundant to me.