Whats the correct syntax to increment a sequence?

oracle, plsql

Solution

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?

Problem

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?

Original source