How to Retrieve the Current Value of an Oracle Sequence Without Increment It

How to retrieve the current value of an oracle sequence without increment it?

SELECT last_number
  FROM all_sequences
 WHERE sequence_owner = '<sequence owner>'
   AND sequence_name = '<sequence_name>';

You can get a variety of sequence metadata from user_sequences, all_sequences and dba_sequences.

These views work across sessions.

EDIT:

If the sequence is in your default schema then:

SELECT last_number
  FROM user_sequences
 WHERE sequence_name = '<sequence_name>';

If you want all the metadata then:

SELECT *
  FROM user_sequences
 WHERE sequence_name = '<sequence_name>';

Hope it helps...

EDIT2:

A long winded way of doing it more reliably if your cache size is not 1 would be:

SELECT increment_by I
  FROM user_sequences
 WHERE sequence_name = 'SEQ';

      I
-------
      1

SELECT seq.nextval S
  FROM dual;

      S
-------
   1234

-- Set the sequence to decrement by 
-- the same as its original increment
ALTER SEQUENCE seq 
INCREMENT BY -1;

Sequence altered.

SELECT seq.nextval S
  FROM dual;

      S
-------
   1233

-- Reset the sequence to its original increment
ALTER SEQUENCE seq 
INCREMENT BY 1;

Sequence altered.

Just beware that if others are using the sequence during this time - they (or you) may get

ORA-08004: sequence SEQ.NEXTVAL goes below the sequences MINVALUE and cannot be instantiated

Also, you might want to set the cache to NOCACHE prior to the resetting and then back to its original value afterwards to make sure you've not cached a lot of values.

Get oracle sequenve next value without change it

You should definitely not rely on never missing a value in a sequence, as they optimise for concurrency over sequential numbering. There are quite a few situations in which a number can be "lost".

Furthermore, the value visible in the dba_sequences may not be the actual next value, as the numbers are assigned from an in-memory cache. The underlying sequence metadata table has no data on the usage of that cache. You should also bear in mind that in a RAC system each instance has its own cache of sequence numbers.

You might describe the problem you are trying to solve, as it could be that sequences are not an appropriate mechanism for you.

Best way to reset an Oracle sequence to the next value in an existing column?

These two procedures let me reset the sequence and reset the sequence based on data in a table (apologies for the coding conventions used by this client):

CREATE OR REPLACE PROCEDURE SET_SEQ_TO(p_name IN VARCHAR2, p_val IN NUMBER)
AS
   l_num   NUMBER;
BEGIN
   EXECUTE IMMEDIATE 'select ' || p_name || '.nextval from dual' INTO l_num;

   -- Added check for 0 to avoid "ORA-04002: INCREMENT must be a non-zero integer"
   IF (p_val - l_num - 1) != 0
   THEN
      EXECUTE IMMEDIATE 'alter sequence ' || p_name || ' increment by ' || (p_val - l_num - 1) || ' minvalue 0';
   END IF;

   EXECUTE IMMEDIATE 'select ' || p_name || '.nextval from dual' INTO l_num;

   EXECUTE IMMEDIATE 'alter sequence ' || p_name || ' increment by 1 ';

   DBMS_OUTPUT.put_line('Sequence ' || p_name || ' is now at ' || p_val);
END;

CREATE OR REPLACE PROCEDURE SET_SEQ_TO_DATA(seq_name IN VARCHAR2, table_name IN VARCHAR2, col_name IN VARCHAR2)
AS
   nextnum   NUMBER;
BEGIN
   EXECUTE IMMEDIATE 'SELECT MAX(' || col_name || ') + 1 AS n FROM ' || table_name INTO nextnum;

   SET_SEQ_TO(seq_name, nextnum);
END;

How to Retrieve an Oracle Sequence value?

To get back a string value representing a sequence value you'll need to convert the sequence's NEXTVAL to a character string using TO_CHAR; something like

SELECT TO_CHAR(MY_SCHEMA.MY_SEQUENCE.NEXTVAL, 'TM9') FROM DUAL

Don't know C# well enough to advise on that, but you can probably work it out from there.

Share and enjoy.

How Can I get the last inserted sequence value for respective to a web session in JSP and Oracle?

I'm not sure to understand. But simply said, a SENQUENCE ensure uniqueness of the generated number among concurrent transactions/connections. Unless if the sequence was created with the CYCLE option, from within a transaction, you can rely on a strictly monotonically increasing (resp. decreasing) numbering. But not from the absence of gap (probably what you where expecting when talking about "sequential numbers").

Worth mentioning that sequence numbers never go backward. When someone acquires a value, it is "consumed" from the sequence and will never get back inside (beside CYCLE) -- even if you rollback the current transaction.

From the doc (emphasis mine):

When a sequence number is generated, the sequence is incremented, independent of the transaction committing or rolling back. If two users concurrently increment the same sequence, then the sequence numbers each user acquires may have gaps, because sequence numbers are being generated by the other user. One user can never acquire the sequence number generated by another user. After a sequence value is generated by one user, that user can continue to access that value regardless of whether the sequence is incremented by another user.

---

My JSP is a little bit ... "rusty", but something like that will work as expected:

<sql:update dataSource="${ds}" var="result">
INSERT INTO member(....) values(seq_form_no.nextval,....);
</sql:update>

<sql:query dataSource="${ds}" var="last_inserted_member_id">
SELECT seq_form_no.currval FROM DUAL;
</sql:query>

---

Related Topics