Results 1 to 4 of 4
  1. #1
    Join Date
    May 2003
    Posts
    9

    Exclamation Unanswered: Selecting from sequence a specific number

    Dears,
    I'm having small requirment with a sequence.

    Lets assume that I have sequence named "yyy_seq"
    If I executed "select yyy_seq.nextval from dual"
    ==> it will get me the value of next item in the sequence and advance the pointer of the sequence for the value after it.

    And
    if I executed "select yyy_seq.nextval from table_zzz"
    ==> it will get me result set of range of sequence starting from next value and ending with a value equals the start + # of rows in the table_zzz
    i.e.:
    Next value
    Next value + 1
    Next value + 2
    ...
    ...
    ...
    Next value + # of rows in table_zzz

    My need is:
    ------------
    I'm having a sequence, and I want to select range of values starting with the next value of this sequence and with length x, this specific value (x) is not known untill run time, and dynamic.

    I have other solutions but there are problems in each one:
    1- Loop x times over "select yyy_seq.nextval from dual" ==> From performance wise is not acceptable, I may have 1000 items so it will be very slow.

    2- Get the first item in the sequence and then alter the sequence to start with this value + x, as I don't have this privilege on DB.

    What I think should do the trick is to have the select statment return any dummy rows so generate the sequence as I wish, but it seems I can't figure it out!!!!!!!!!!

    Thanks

  2. #2
    Join Date
    Sep 2002
    Location
    UK
    Posts
    5,171
    Provided Answers: 1
    You can write a "pipelined" function to return an arbitrary number of rows like this:
    Code:
    SQL> create type number_tab is table of number;
      2  /
    
    Type created.
    
    SQL> create or replace function get_nums (n in natural)
      2  return number_tab
      3  pipelined
      4  is
      5  begin
      6    for i in 1..n loop
      7      pipe row(i);
      8    end loop;
      9    return;
     10  end;
     11  /
    
    Function created.
    
    SQL> select * from table(get_nums(11));
    
    COLUMN_VALUE
    ------------
               1
               2
               3
               4
               5
               6
               7
               8
               9
              10
              11
    
    11 rows selected.
    
    SQL> create sequence my_seq;
    
    Sequence created.
    
    SQL> select my_seq.nextval from table(get_nums(11));
    
       NEXTVAL
    ----------
             1
             2
             3
             4
             5
             6
             7
             8
             9
            10
            11
    
    11 rows selected.

  3. #3
    Join Date
    May 2004
    Location
    Dominican Republic
    Posts
    721
    As I understand it, what is wrong with.. select yyy_seq.nextval some_big_table_you_know_has_as_many_as_your_sequen ce_number where rownum <= how_many_sequences .. if they are just 2K or less, you can use the all_objects table in this case.

    Also, If I understand correctly what you are trying to do, it seems that your approach will fail on a multi-user environment in the case that this sequence is part of a primary key number. What happens when two users start the same process at relative times ?

  4. #4
    Join Date
    Apr 2004
    Location
    Toronto, Canada
    Posts
    249
    Since you require a block of numbers at a time, a sequence may not be the best way to get them.

    The simplest way is to have a table that stores one row of data with one column.
    Have a small function that does the following:
    When a request for numbers comes in, use a "select for update" cursor to get the current row, increment it by the desired total numbers required, then update this table. After all this is done, send the number range back to the program that needs it.

    This way, you should not have any problems with two different users requiring a block of numbers at the same time.

    Hope that helps.

    Ravi

Posting Permissions

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