CODEWITHSUNDEEP
oracle-databaseplsql
Corwin01
Corwin01

Reputation: 479

Whats the correct syntax to increment a sequence?

I have a function that looks like this. 

    Function GetNewBatch ( CourseName Varchar2 ) Return RefCursor
    As
      Results RefCursor;
      CourseId Number;
    Begin

      CourseId := Courselist.GetId( CourseName );

      Open    Results For 
      Select  q.user_abn           UserAbn,
              q.completed_t        DateCompleted,
                                   CourseName,
              q.batch_n            BatchId
      From    GAK.GAKHR02_ACK q
      Where   q.crse_i  = CourseId
      And     q.batch_n is null
      And     rownum < 1000;

      GAK.SEQ1_GAKHR03.NextVal;
      Return Results;
    End;

I want to increment the sequence after the select, but SQL Developer is giving me the error:

"Error(194,5): PLS-00313: 'NEXTVAL' not declared in this scope."

How can I do this?

Upvotes: 3

Views: 2071

Answers (1)

Ben
Ben

Reputation: 52853

It's because you're not assigning the nextval to a variable. Assume that .nextval is a function of some description. It's not, Oracle actually describe it as a psuedocolumn; something used in the same context as a column but not written to the disk.

How you increment a sequence depends on the version of Oracle you're using. Prior to 11G you could do the following in a PL/SQL block:

declare    
   i number;    
begin

   select my_sequence.nextval
     into i
     from dual;

   insert into my_table(id)
   values(my_sequence.nextval);

   update my_table
      set id = my_sequence.nextval;

end;

In 11G this changed slightly so you could also assign the "return" value from .nextval to a variable:

declare
   l_next_val number;
begin
   l_next_val := my_sequence.nextval;
end;

The reason for PLS-00313 is that Oracle is assuming that SEQ1_GAKHR03 is a package or some other object and .nextval is a sub-type of this object.

It's very unusual to increment a sequence without actually using the value. Is this intended behaviour?

Upvotes: 4

Related Questions