Documentation  ·  Curating

Working with sequences

A selected sequence yields exactly one tool:

<owner>_<sequence>_nextval

It returns the next value, and nothing else.

If you have never used an Oracle sequence before…

• It’s guaranteed to be unique, but may not actually be sequential
• The exception to unique is CYCLE: a sequence created with it wraps at MAXVALUE and re-issues values it has already handed out.
• Each Oracle instance caches a small number in RAM for immediate use. Each one can only be handed out once.
• However, if the instance dies you may end up with a gap in the sequence numbering
• Oracle RAC has one cache per instance, so sequences will not be handed out sequentially. CREATE SEQUENCE … ORDER does guarantee ordering across instances, at a cost in performance that is why it is rarely used.

currval is deliberately not exposed

It is session state, and the session an agent’s call ran on is not one it can reason about — pooled, the next call may land on a different factory entirely. A currval tool would return a number that looked meaningful and was not.

Practical notes

• nextval consumes a value whether or not the agent goes on to use it. Gaps are normal and are not a fault.
• The calling Oracle account needs SELECT on the sequence.