I am trying to get a range of sequence values from an Oracle sequence.
I am using the option as show below using query
SELECT SEQUENCE_NAME.NEXTVAL FROM SYS.DUAL CONNECT BY LEVEL <= 10.
The above SQL gets 10 sequence value.
I just wanted to to check, if the implementation below is safe in a Multi User Environment?
Is the statement show below atomic. i.e. Multi parallel execution of the same function; Would it cause any inconsistencies?
EXECUTE IMMEDIATE 'SELECT SEQUENCE_NAME.NEXTVAL ' ||
'FROM SYS.DUAL CONNECT BY LEVEL <= ' || TO_CHAR(i_quantity)
BULK COLLECT INTO v_seq_list;
FUNCTION select_sequence_nextval_range(
i_quantity IN INTEGER)
RETURN INTEGER IS
o_nextval INTEGER;
v_seq_list sequence_list;
BEGIN
EXECUTE IMMEDIATE 'SELECT SEQUENCE_NAME.NEXTVAL ' ||
'FROM SYS.DUAL CONNECT BY LEVEL <= ' || TO_CHAR(i_quantity)
BULK COLLECT INTO v_seq_list;
-- Get the first poid value.
o_nextval := v_seq_list(1);
RETURN o_nextval;
END select_sequence_nextval_range