OraFAQ Forum: SQL & PL/SQL » alter sequence

Home » SQL & PL/SQL » SQL & PL/SQL » alter sequence

Show: Today's Messages :: Polls :: Message Navigator
E-mail to friend

alter sequence [message #216945]

Wed, 31 January 2007 00:28

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

Report message to a moderator

Re: alter sequence [message #216947 is a reply to message #216945]

Wed, 31 January 2007 00:40

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

Report message to a moderator

Re: alter sequence [message #216960 is a reply to message #216947]

Wed, 31 January 2007 01:29

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

Report message to a moderator

Re: alter sequence [message #216984 is a reply to message #216960]

Wed, 31 January 2007 02:59

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

Frank
Messages: 7901
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

Report message to a moderator

Re: alter sequence [message #216991 is a reply to message #216945]

Wed, 31 January 2007 03:17

chinmay_uce02
Messages: 11
Registered: January 2007

Junior Member

hi frank,
thanks a lot. i got the answer to my query.

thanks
chinmay

Report message to a moderator

Previous Topic:	Temporary Table - Insertion dynamically
Next Topic:	How to get Client IP?

Goto Forum:

-=] Back to Top [=-

[ Syndicate this forum (XML) ] [

]

Current Time: Fri Dec 06 17:54:27 CST 2024