Geek asks and answers

What is the correct syntax for increasing the 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 increase the sequence after the selection, but SQL Developer gives me an error:

"Error (194.5): PLS-00313: 'NEXTVAL' is not declared in this area."

How can i do this?

+3
source share
1 answer

This is because you are not assigning a variable to a nextvalvariable. Suppose that .nextvalis a function of some description. It is not, Oracle actually describes it as psuedocolumn; something is used in the same context as the column, but is not written to disk.

Oracle. 11G PL/SQL:

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;

11G , "return" .nextval :

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

PLS-00313 , Oracle , SEQ1_GAKHR03 , .nextval .

. ?

+3

More articles:


All Articles