Sequence in Decode

[bang_dba]

Hi all,

I am using a sequence (from 001 to 999) in Decode as follows.
The Idea is to concatinate the number with 001 or 010. so that the generated number will like TSA001,TSA002.....TS100...TSA999.
The maximum numbe will be 999.

select decode
(length(Emp_Num_SEQ.Nextval),1,'TAS00'||Emp_Num_SEQ.Nextval,2,'TAS0'||Emp_Num_SEQ.Nextval,3,'TAS'||E mp_Num_SEQ.Nextval) from dual


Coz i select once to findout the Lenght and once again to concatinate the value to TAS, i am using the same sequence.
Logically speaking there should be jump of sequences.

But i am getting correct values.

How is this ? Can this create any problems in future ?

[abhaysk]

All the occurence of Emp_Num_SEQ.Nextval will be substituted with its value once per row fetch..

Abhay.

[jmodic]

Originally posted by bang_dba
select decode
(length(Emp_Num_SEQ.Nextval),1,'TAS00'||Emp_Num_SEQ.Nextval,2,'TAS0'||Emp_Num_SEQ.Nextval,3,'TAS'||E mp_Num_SEQ.Nextval) from dual

There is no need to use DECODE at all. It would be much simplier if you code it like this:

select 'TAS'|| ltrim(to_char(Emp_Num_SEQ.Nextval,'099')) from dual;

[slimdave]

Whenevr I see sequences being concatanated to a text string it brings up bad memories of previous projects. Personally I'd rather not see something like that happen - it leads to tears down the road.

[bang_dba]

Originally posted by jmodic
There is no need to use DECODE at all. It would be much simplier if you code it like this:

select 'TAS'|| ltrim(to_char(Emp_Num_SEQ.Nextval,'099')) from dual;

Thanx Jmodic. U r really an Oracle GURU

[bang_dba]

Hi,

Apart from rollback and Cache problem( i am not using cache option),
is there any other reason due to which sequence nubers will jump ?

[tamilselvan]

If nocache option is used for sequence, you will not see gap in the sequence.

Did you see it?

When the sequence is created with cache option, you may see gap in it, only when the shared pool flushed or shutdown abort was used.

Tamil

[patel_dil]

Originally posted by tamilselvan
If nocache option is used for sequence, you will not see gap in the sequence.

Tamil

What will happen when after sequence is generated and rollback is issued? Gaps are generated, no?

[bang_dba]

Thanx Tamil

Originally posted by patel_dil
What will happen when after sequence is generated and rollback is issued? Gaps are generated, no?

Yes, For rollback, the sequence number is skipped.