0

We have one requirement where different database sequences needs to be maintained for different channels. EX: ABC-SQN1, XYZ-1, and the Sequence nos needs to be incremented based on channels. Is there a way we can achieve it.

Thanks

Sushma
  • 121
  • 4
  • 18
  • Please explain the reasoning behind the requirement "different database sequences needs to be maintained for different channel". Also, please provide clear example. Thanks. – David דודו Markovitz Oct 14 '16 at 11:24

1 Answers1

1

Your question is unclear. Please describe requirements more datailed. I understand you want to have a few sequences and increment them conditionally so:

create sequence chanel1_seq INCREMENT BY 1 START WITH 1;
create sequence chanel2_seq INCREMENT BY 1 START WITH 1;
create sequence chanel3_seq INCREMENT BY 1 START WITH 1;

and then access sequence set by function not directly:

create or replace function get_seq_val(chanell in varchar2) return varchar2 is
begin
if (chanell = 'CH1') then
return 'CH1' || chanel1_seq.nextval;
elsif (chanell = 'CH2') then
return 'CH2' || chanel2_seq.nextval;
elsif (chanell = 'CH3') then
return 'CH3' || chanel3_seq.nextval;
end if;
return '';
end;

And get value by:

select get_seq_val('CH1') from dual;
Kacper
  • 4,798
  • 2
  • 19
  • 34
  • Hi Kacper, Thanks for the help, our requirement is same you explained here. i have updated the code as suggested. but facing issues with the dynamic Sequence name creation. I'm new to PL-SQL could you please suggest whether it supports or not. code: 'create or replace function get_seq_val(chanelname in varchar2) return varchar2 is Channel_identifier VARCHAR2(10); Sequencename varchar2(10):='_seq'; SQName varchar2(10); begin Channel_identifier:=chanelname; SQName := concat(Channel_identifier,Sequencename); return SQName.nextval; end;' – Sushma Oct 18 '16 at 03:11