Results 1 to 4 of 4
  1. #1
    Join Date
    Dec 2003
    Location
    USA
    Posts
    33

    Unanswered: Oracle Sequences...

    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...

    Code:
    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
    A friend in need is a friend in deed

  2. #2
    Join Date
    Dec 2003
    Posts
    1,074
    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

  3. #3
    Join Date
    Apr 2008
    Posts
    6
    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

  4. #4
    Join Date
    Mar 2007
    Posts
    623
    > 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.

Posting Permissions

  • You may not post new threads
  • You may not post replies
  • You may not post attachments
  • You may not edit your posts
  •