Home » SQL & PL/SQL » SQL & PL/SQL » how can I get sequence's current value
how can I get sequence's current value [message #220479] Tue, 20 February 2007 16:48 Go to next message
abo_mahmoud
Messages: 31
Registered: May 2006
Location: UAE
Member
How can I get sequence's current value or last number without setting next value(in the same session)
In dba_sequences I find the last number which is with cache -I don't need it-

I need the true one ..........
Re: how can I get sequence's current value [message #220481 is a reply to message #220479] Tue, 20 February 2007 17:18 Go to previous messageGo to next message
ebrian
Messages: 2794
Registered: April 2006
Senior Member
select <sequence_name>.currval from dual;
Re: how can I get sequence's current value [message #220505 is a reply to message #220479] Wed, 21 February 2007 00:36 Go to previous messageGo to next message
Frank
Messages: 7880
Registered: March 2000
Senior Member
abo_mahmoud wrote on Tue, 20 February 2007 23:48
How can I get sequence's current value or last number without setting next value(in the same session)

If you don't want to use nextval in the same session, then this number isn't really meaningful (apart from the fact that you simply can't retrieve it).
It is possible somebody issues an insert-statement in another session, which fires a trigger that gets a nextval. Now this user issues a rollback.
Why would you want this sequence-number? It is not related to any record in the database.
So, either you do use the value stored in dba_sequences, or you ask for a nextval yourself (in your own session).

[Updated on: Wed, 21 February 2007 00:37]

Report message to a moderator

Re: how can I get sequence's current value [message #220546 is a reply to message #220479] Wed, 21 February 2007 03:48 Go to previous messageGo to next message
abo_mahmoud
Messages: 31
Registered: May 2006
Location: UAE
Member
Sorry ......
I don't need current value indeed ;I need last available number
As you know ORACLE has a an exception on alter sequence which is you can't put the maxvalue smaller than last available number -which is I need-

In case of nocache the objective is last_number from dba_sequences but in case of cache the last number in dba_sequences doesn't give the last available which ORACLE put a restrict on it.......

I'm trying to program an oracle admin editor which require to alter any object and in our case SEQUENCE
Re: how can I get sequence's current value [message #220550 is a reply to message #220546] Wed, 21 February 2007 04:17 Go to previous messageGo to next message
Frank
Messages: 7880
Registered: March 2000
Senior Member
Then you could catch the error if someone would give a too low value and display an error stating this value was already assigned.
Re: how can I get sequence's current value [message #220737 is a reply to message #220479] Wed, 21 February 2007 18:02 Go to previous messageGo to next message
abo_mahmoud
Messages: 31
Registered: May 2006
Location: UAE
Member
I got the solution ............
I hope you got my problem ,has any one another solution

ALTER SEQUENCE S NOCACHE;
--Get the next value
SELECT LAST_NUMBER FROM USER_SEQUENCES WHERE SEQUENCE_NAME = 'S';

ALTER SEQUENCE S CACHE 20;
--assuming 20 is the original cache size
Re: how can I get sequence's current value [message #220848 is a reply to message #220479] Thu, 22 February 2007 06:38 Go to previous messageGo to next message
MarcL
Messages: 455
Registered: November 2006
Location: Connecticut, USA
Senior Member
I don't think you want to be messing with altering the sequences on a regular basis.

Catching the exception and displaying an appopriate message to the usesr as previously suggested is probably the way to go.
Re: how can I get sequence's current value [message #220923 is a reply to message #220479] Thu, 22 February 2007 14:54 Go to previous message
IT Guru
Messages: 59
Registered: January 2007
Member
SELECT * FROM USER_SEQUENCES WHERE SEQUENCE_NAME = <sequence_name>

you will find last value of sequence
Previous Topic: procedure to load excel file into the database
Next Topic: Get weekday name in PL/SQL
Goto Forum:
  


Current Time: Sat Dec 10 18:32:51 CST 2016

Total time taken to generate the page: 0.21180 seconds