Results 1 to 4 of 4
  1. #1
    Join Date
    Jan 2004
    Posts
    7

    Unanswered: how to do bulk generation of sequence

    is it possible to generate bulk seuence numbers from a single sql query. Lets say there is a sequence name "test_seq". The curent value on that sequence is lets say 10, if i want to get the next 20 sequence in a sql query, how i will do that, i want to get those 20 values in the resultset through java.

    Thanks

  2. #2
    Join Date
    Apr 2003
    Posts
    191

    Re: how to do bulk generation of sequence

    Hi,

    documentation does not suggest there is a way to retrieve a couple of different NEXTVALs for YOURSEQENCE in a single query. But there are other options:

    a) create a sequence with an INCREMENT BY 20 clause, and let the application do the filling of the gaps or
    b) retrieve 20 NEXTVALs through the application or
    c) create a table function making use of procedure language, retrieve 20 NEXTVALs in a loop

    Would be interesting to know what you are going to settle with.

    Johann
    Originally posted by vijpan
    is it possible to generate bulk seuence numbers from a single sql query. Lets say there is a sequence name "test_seq". The curent value on that sequence is lets say 10, if i want to get the next 20 sequence in a sql query, how i will do that, i want to get those 20 values in the resultset through java.

    Thanks

  3. #3
    Join Date
    Aug 2001
    Location
    UK
    Posts
    4,650

    Re: how to do bulk generation of sequence

    d) Use a recursive SQL to retrieve 20 values

    Cheers
    Sathyaram

    Originally posted by jsander
    Hi,

    documentation does not suggest there is a way to retrieve a couple of different NEXTVALs for YOURSEQENCE in a single query. But there are other options:

    a) create a sequence with an INCREMENT BY 20 clause, and let the application do the filling of the gaps or
    b) retrieve 20 NEXTVALs through the application or
    c) create a table function making use of procedure language, retrieve 20 NEXTVALs in a loop

    Would be interesting to know what you are going to settle with.

    Johann
    Visit the new-look IDUG Website , register to gain access to the excellent content.

  4. #4
    Join Date
    Jun 2002
    Location
    UK
    Posts
    525
    Here's a hack...

    with nextvals(counter) as
    (
    values (1)
    union all
    select counter + 1
    from nextvals
    where counter < 20
    )
    select nextval for your_sequence
    from nextvals
    ;

    Damian

Posting Permissions

  • You may not post new threads
  • You may not post replies
  • You may not post attachments
  • You may not edit your posts
  •