[versoft]

Hi Friends,

Thanks all for the last solution.

How do i get the last sequence number without calling the nextval.

When i call currval without calling the nextval i get the following error...

select LOSS_EVT_ID_SEQ.CURRVAL from dual


ORA-08002: sequence LOSS_EVT_ID_SEQ.CURRVAL is not yet defined in this session

Is there any way to fetch last sequence number?

Thanks in advance,
Amit

[chuck_forbes]

If the sequence number is that meaningful, then you need to come up with another approach for your primary key. The last sequence number available, may not be the last sequence number used. Sequence values are regularly purged from memory during normal database use, and so you'll see gaps. Also, if someone grabs the nextval, but then rolls back their transaction, the sequence number is not rolled back.

---=Chuck

[Sugam Khetrapal]

Can someone please tell me what is the escape sequence for ' ?
I need to retrieve a number from a query, but the query will fetch the number only if it in enclosed in '0123423' i.e. single inverted comma

[flyboy]

> Is there any way to fetch last sequence number?

Yes, but only in case it was generated before by NEXTVAL in the current transaction.

Documentation links:
Sequence pseudocolumns:

Before you use CURRVAL for a sequence in your session, you must first initialize the sequence with NEXTVAL.

CREATE SEQUENCE Command:

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.

By the way, how would you define "last sequence number" in multi-user environment? You may implement it yourself, but then you need to serialize requests for the values.