generating same positive and negative number in Oracle sequence

Question

How can I generate the below numbers through sequence in oracle

2
-2
2
-2
2
-2

And so on. I can generate 1,2,1,2,1,2 through cycle but above I am not able to use logic.

Answer 1

This seems like an odd requirement for a sequence, but if you really want to then you can do:

create sequence weird_seq
start with 2
increment by 4
maxvalue 2
minvalue -2
cycle
nocache;

select weird_seq.nextval from dual
connect by level <= 10;

   NEXTVAL
----------
         2
        -2
         2
        -2
         2
        -2
         2
        -2
         2
        -2

The increment 4 is so it steps from -2 to +2. The maxval 2 is so the next call cycles round to the minval -2. And it needs to start with +/-2. The nocache is unfortunate but necessary, otherwise you get "ORA-04013: number to CACHE must be less than one cycle".

If you're just using these numbers as part of a query then you could do it with a hierarchical query instead:

select 2 * (2 * mod(level, 2) - 1)
from dual
connect by level <= 10;

2*(2*MOD(LEVEL,2)-1)
--------------------
                   2
                  -2
                   2
                  -2
                   2
                  -2
                   2
                  -2
                   2
                  -2

but then running multiple times will always start at +2, which might not be what you want. (Depends how it'll be used...)

You could potentially also generate the number on the fly from existing data using an analytic query, but again, it depends what you'll use this for.

Answer 2

Here is simple way of doing the same using CASE and MOD

SELECT CASE WHEN mod(level, 2) = 1 THEN 2 ELSE -2 END sequence 
FROM dual
CONNECT BY LEVEL <= 10;

Answer 3

Use a rownumber selection in your update statement.

UPDATE yourtable
SET yourfield = yourfield*-1
WHERE yourfield IN
(SELECT yourfield FROM 
(SELECT rownum rn, y."yourfield" yourfield FROM yourtable y)
WHERE MOD(rn, 2) = 0)

Answer 4

I can suggest a method by using PL/SQL :

SQL> set serveroutput on;
SQL> Create sequence my_seq start with 2 minvalue -4;     
Sequence created

SQL> Create or Replace Procedure Pr_Get_MySeq( o_val out pls_integer ) is
  2     i_seq_name varchar2(35):= 'my_seq';
  3  begin
  4      execute immediate 'select '||i_seq_name||'.nextval from dual' INTO o_val;
  5      execute immediate 'alter sequence '||i_seq_name||' increment by ' ||to_char(-2*o_val) || ' minvalue -4';
  6  end;     
  7  /     
Procedure created

SQL> var o_val number;
SQL> begin
  2    Pr_Get_MySeq( :o_val );
  3  end;
  4  /

PL/SQL procedure successfully completed
o_val
---------
2

SQL> /

PL/SQL procedure successfully completed
o_val
---------
-2

SQL> /

PL/SQL procedure successfully completed
o_val
---------
2