Create sequence with specific set of values in oracle

Question

I would like to create a sequence in oracle that will consists of two values(1, -1).

Sequence will be 1,-1,1,-1,1,-1

Is it possible to create this type of sequence in oracle that will alternate between this two values only?

Is this possible using standard Create sequence syntax in oracle?

Oracle doesn't guarantee a sequence to have no gaps. So it *could* happen with a standard sequence to get 1,2,5,6,7... Same for your sequence; you'd usually get 1,-1,1,-1, but it *could* always happen you get 1,1,-1,1. What do you want to achieve with that sequence anyhow? — Thorsten Kettner, Sep 29 '15 at 07:42
Okay. So the answer is yes, just as a_horse_with_no_name is showing in their answer, but it's not guaranteed to work gap-free - which is the case for any sequence, however. So we have a theoretical answer to a theoretical question, and in real life we would probably never use such construct. — Thorsten Kettner, Sep 29 '15 at 08:00

score 3 · Accepted Answer · answered Sep 29 '15 at 07:27

3

Yes, this is possible:

create sequence seq_alternate 
   minvalue -1
   maxvalue 1
   start with 1
   increment by 2
   nocache
   cycle;

answered Sep 29 '15 at 07:27

score 1 · Answer 2 · answered Sep 29 '15 at 07:18

1

One way to create such a sequence is to raise -1 to different powers. If the power is even, you'll get 1 and if its odd, you'll get -1. E.g.:

SELECT POWER(-1, LEVEL + 1)
FROM   dual
CONNECT BY LEVEL <= 6

answered Sep 29 '15 at 07:18

Mureinik

Refract this code as a FUNCTION and there you go ready with the sequence you want. :) – Avrajit Roy Sep 29 '15 at 07:37

2 Answers2