title: How to implement a custom oracle sequence?
body: <p>The oracle sequence is designed to return auto-increasing numbers. I am wondering that can I implement a customized sequence that returns from values pool I putted in, so I can tell the sequence what are expected to return.</p>
  
  <p>Anyway to implement this? or alternative, like adding trigger, or oracle functions or anything else?    </p>
  
  <p>Or even to use a table the save values, but how to do is best performance, like oralce  sequence</p>
  
  <pre><code>I have this, but not sure is it the best one.
  create table your_table(
     gap_from int,
     gap_to int
  );
  
  insert into your_table values(99999, 9998888);
  insert into your_table values(2, 7);
  insert into your_table values(200, 10000);
  insert into your_table values(10001, 300000);
  
  create table2 as  select
            gap_from,
            1 + nvl(sum(gap_to - gap_from + 1) over (order by gap_from rows between unbounded preceding and 1 preceding), 0) as seq_from,
            sum(gap_to - gap_from + 1) over (order by gap_from) as seq_to
          from your_table;
  
  create sequence your_new_seq;
  
  
  create or replace function get_PK_inside_gap return number as
    new_seq_val number;
    PK_inside_gap number;
  begin
    select your_new_seq.nextval into new_seq_val from dual;
    execute immediate '
      select
        new_seq_val + gap_from - seq_from
      from
        (select :1 as new_seq_val from dual)
        join (
         table2 
        ) on new_seq_val between seq_from and seq_to'
    into PK_inside_gap using new_seq_val;
    return PK_inside_gap;
  end;
  </code></pre>