Currently Being Moderated
I have a question for all the Oracle GURU's out there. I have the following scenario, I will appreciate any help
I have like 100 different sequences. What I want to do is, I want to write a function which take the Sequence Name and the Number of Sequence Numbers required as parameters,
eg
select SEQUENCE_FUNCTION('my_sequence', 1000) from dual;
What this function should return is a list of next 1000 numbers from the sequence my_sequence...for example, if the sequence currval is 1000, it gives me back 1001 till 2000. Now the tricky part, I want this function to be in the Database, and what I want is, if two different sessions call this function concurently, then the one of the sessions block the other from getting the numbers from the sequence till it gets its 1000 numbers, and then the second session can get the numbers
eg session one at 10:00:00 AM where currval is 1000
SELECT SEQUENCE_FUNCTION('my_sequence', 1000) from dual
Returns
1001 to 2000
session two at 10:00:01 AM
SELECT SEQUENCE_FUNCTION('my_sequence', 1000) from dual
Returns
Wait for the first session to finish and then grab the numbers from the sequence to return 2001 to 3000.
I have tried all kind of things, including Bulk Collection and making sure the the first sessions's process dosen't end till all the numbers are grabbed, the session 2 still manage to get the sequence numbers whihc it shouldn't get...what I am getting is as following
Session 1 function returns 1001 to 1200, then 1201, 1203, 1205 etc and goes beyound 2000
Session 2 function returns 1202, 1204, 1206 etc
I will really appreciate any help regrading this issue. Thanks again for lookign at my issue.