If you need to issue them in sets of 1000, then why generate the individual values (and why try to isolate 1000 individual values)? Just record the starting values as 1000, 2000, etc. and calculate the rest from there.
DELETE FROM KEY_TABLE WHERE KEY_VALUE = (SELECT MIN(KEY_VALUE) FROM KEY_TABLE ) RETURNING KEY_VALUE INTO return_value;
mkmunir wrote:Then the client wants a very slow application that supports a maximum of one process at a time.
The client do not want any holes in the sequence numbers and want the numbers to be consecutive
Now retrieve blocks of numbers as follows:
create table my_sequences (seq_name varchar2(10) not null primary key ,current_value number not null); insert into my_sequences values('seq1',0); insert into my_sequences values('seq2',0); -- etc... create type num_t as table of number; / create or replace function seq_fun(p_seq_name in varchar2, p_block_size in number) return num_t PIPELINED as pragma autonomous_transaction; pl_current_value number; begin -- select current_value into pl_current_value from my_sequences where seq_name = p_seq_name for update of current_value; -- update my_sequences set current_value = current_value + p_block_size where seq_name = p_seq_name; -- commit; -- for i in pl_current_value..pl_current_value+p_block_size - 1 loop pipe row(i); end loop; -- end; /
Of course the question is whether you really need this to be a query that returns the sequence numbers each as one row.
select column_value as seqnum from table(seq_fun('seq1',100));
mkmunir wrote:Then sequences are NOT the appropriate tool to use.
The client do not want any holes in the sequence numbers and want the numbers to be consecutive for every process which goes and grabs the numbers.....every process has it own name and gets populated in the table....example
If your application can never lose sequence numbers, then you cannot use Oracle sequences, and you may choose to store sequence numbers in database tables. Be careful when implementing sequence generators using database >tables. Even in a single instance configuration, for a high rate of sequence values generation, a performance overhead is associated with the cost of locking the row that stores the sequence value.
The client do not want any holes in the sequence numbers and want the numbers to be consecutive for every process which goes and grabs the numbers....And what makes your client want that?
mkmunir wrote:As you've previously pointed your client to this thread, hopefully they will read this.
The client do not want any holes in the sequence numbers and want the numbers >to be consecutive for every process which goes and grabs the numbers.....every >process has it own name and gets populated in the table....example