Home » SQL & PL/SQL » SQL & PL/SQL » alter sequence
alter sequence [message #216945] Wed, 31 January 2007 00:28 Go to next message
chinmay_uce02
Messages: 11
Registered: January 2007
Junior Member
hi all
is it possible to decrement the current value of an oracle sequence?


thanks in advance
chinmay
Re: alter sequence [message #216947 is a reply to message #216945] Wed, 31 January 2007 00:40 Go to previous messageGo to next message
Mohammad Taj
Messages: 2412
Registered: September 2006
Location: Dubai, UAE
Senior Member

Hi,
yes you can decrement but <= MAXVALUE parameter.
SQL> create sequence pp3
  2  increment by 1
  3  start with 1
  4  minvalue 1
  5  maxvalue 100
  6  cache 20;

Sequence created.

SQL> create table pj ( no number , no1 number);

Table created.

SQL> begin for x in 1..50 loop insert into pj values ( x,pp3.nextval); end loop;
 end;
  2  /

PL/SQL procedure successfully completed.


SQL> alter sequence pp3
  2  maxvalue 50;

Sequence altered.

SQL> alter sequence pp3
  2  maxvalue 49;
alter sequence pp3
*
ERROR at line 1:
ORA-04009: MAXVALUE cannot be made to be less than the current value


SQL>



regards
Taj





Re: alter sequence [message #216960 is a reply to message #216947] Wed, 31 January 2007 01:29 Go to previous messageGo to next message
chinmay_uce02
Messages: 11
Registered: January 2007
Junior Member
hi Taj,
thanks for ur reply. but my query is like can we change the current value pointed by the sequence. as in ur examlpe after inserting 50 records in the table the sequence.currval points to 50. can we decrement this value so that sequence.currval will point to 40? that is in next insert values will go as 40,41,42...

thanks
chimnay
Re: alter sequence [message #216984 is a reply to message #216960] Wed, 31 January 2007 02:59 Go to previous messageGo to next message
Mohammad Taj
Messages: 2412
Registered: September 2006
Location: Dubai, UAE
Senior Member

.
Great Frank... Cool

[Updated on: Wed, 31 January 2007 03:02]

Report message to a moderator

Re: alter sequence [message #216985 is a reply to message #216960] Wed, 31 January 2007 03:00 Go to previous messageGo to next message
Frank
Messages: 7880
Registered: March 2000
Senior Member
Before you continue on this (yes, it IS possible), ask yourself why you want this.
If you think you can create gap-free sequences, forget it. If you use a sequence, in the end you either end up with gaps, or with a single-user system.

SQL> create sequence faq_seq
  2  nocache;

Sequence created.

SQL>
SQL> declare
  2    l number;
  3  begin
  4    for i in 1..1000
  5    loop
  6      select faq_seq.nextval
  7      into   l
  8      from   dual;
  9    end loop;
 10  end;
 11  /

PL/SQL procedure successfully completed.

SQL> select faq_seq.nextval from dual;

   NEXTVAL
----------
      1001

SQL> alter sequence faq_seq increment by -50;

Sequence altered.

SQL> select faq_seq.nextval from dual;

   NEXTVAL
----------
       951

SQL> alter sequence faq_seq increment by 1;

Sequence altered.

SQL> select faq_seq.nextval from dual;

   NEXTVAL
----------
       952
Re: alter sequence [message #216991 is a reply to message #216945] Wed, 31 January 2007 03:17 Go to previous message
chinmay_uce02
Messages: 11
Registered: January 2007
Junior Member
hi frank,
thanks a lot. i got the answer to my query.

thanks
chinmay
Previous Topic: Temporary Table - Insertion dynamically
Next Topic: How to get Client IP?
Goto Forum:
  


Current Time: Mon Dec 05 07:08:41 CST 2016

Total time taken to generate the page: 0.11277 seconds