How to implement a custom oracle sequence?

Question

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.

Anyway to implement this? or alternative, like adding trigger, or oracle functions or anything else?

Or even to use a table the save values, but how to do is best performance, like oralce sequence

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;

It could depend on what you want to get, can you give an example? — A.B.Cade
– A.B.Cade, Commented Mar 10, 2013 at 14:28
For example, I want to develop a customized sequence which return number from the ranges [2,7], [200,10000], [10001, 300000], [99999,9998888] and so on... so here I want to get the auto-increasing from specific range. — C.c
– C.c, Commented Mar 10, 2013 at 14:39

Jeffrey Kemp · Accepted Answer · 2013-03-12 05:05:54Z

1

To return only values from range 5 to 10:

 create sequence seq1 start with 5 maxvalue 10;

To return only values from 100000 to 999990, in jumps of 10:

 create sequence seq2 start with 100000 maxvalue 999990 increment 10;

edited Mar 12, 2013 at 5:05

Jeffrey Kemp

60.5k15 gold badges111 silver badges161 bronze badges

answered Mar 10, 2013 at 16:08

Frank Schmitt

30.5k13 gold badges79 silver badges111 bronze badges

Sign up to request clarification or add additional context in comments.

Comments

WestCoastProjects · Accepted Answer · 2013-03-10 16:00:12Z

0

Create a table with the entries you want, then pin the table in memory for good performance. When the values get 'used' you can update them to set a status flag.

In your query from that in memory pinned table, the query can select the smallest entry that is not 'used'. Or you can make the query select on other criteria as you mentioned you want that flexibility.

answered Mar 10, 2013 at 16:00

WestCoastProjects

63.9k109 gold badges368 silver badges638 bronze badges

Comments

Egor Skriptunoff · Accepted Answer · 2013-03-10 16:09:05Z

You can create usual sequence and modify its result according to your gap list.

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 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 (
        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
      ) on new_seq_val between seq_from and seq_to'
  into PK_inside_gap using new_seq_val;
  return PK_inside_gap;
end;

Now call get_PK_inside_gap to get your sequence of not used PK: 2,3,4,5,6,7,200,201,...

Thiyagu ATR · Accepted Answer · 2013-03-11 10:04:48Z

0

dude i think this might help you,usually once you create sequence you can't assign start with parameter,so better drop that sequence and recreated it;

create or replace
procedure SEQ_alter(start_with in number,end_with in number,seq_name varchar2)
   as
   sql_qury varchar2(148);
   drop_qury varchar2(148);
   begin
   drop_qury:='drop sequence '||seq_name ;
   execute immediate drop_qury;
   sql_qury:='create sequence '||seq_name|| ' start with '|| to_number(start_with ) ||' maxvalue '|| to_number(end_with);

   execute immediate sql_qury;
   end;

edited Mar 11, 2013 at 10:04

answered Mar 11, 2013 at 7:39

Thiyagu ATR

2,2747 gold badges31 silver badges44 bronze badges

Collectives™ on Stack Overflow

How to implement a custom oracle sequence?

4 Answers 4

Comments

Comments

Comments

Comments

Your Answer

Hot Network Questions

Collectives™ on Stack Overflow

4 Answers 4

Comments

Comments

Comments

Comments

Your Answer

Sign up or log in

Post as a guest

Related