Re: reading sequence.CURRVAL

From Oracle 7 Server Reference : CHAPTER 2 : Static Data Dictionary Views :

ALL_SEQUENCES

SEQUENCE_OWNER	Name of the owner of the sequence
SEQUENCE_NAME	Sequence name
MIN_VALUE		Minimum value of the sequence
MAX_VALUE		Maximum value of the sequence
INCREMENT_BY	Value by which sequence is incremented
CYCLE_FLAG		Does sequence wrap around on reaching limit
ORDER_FLAG		Are sequence numbers generated in order
CACHE_SIZE		Number of sequence numbers to cache
LAST_NUMBER	Last sequence number written to disk.
			If a sequence uses caching, the number
			written to disk is the last number
			placed in the sequence cache. This
			number is likely to be greater than the
			last sequence number that was used.

If you ALTER SEQUENCE xxxx NOCACHE ; then ALL_SEQUENCES.LAST_NUMBER will provide what you need.
This has the additional benefit that none of the sequence numbers are lost when the machine crashes.

Baz

On Thu, 23 Apr 1998 16:44:53 -0700, Bill Dietrich <bill_dietrich_at_wayfarer.com> wrote:

>Okay, I see your point, and it is valid.
>
>But I guess I would use a PL/SQL temp variable or something
>to accomplish your example:
>
>id = seq.nextval
>insert into parent table ( ...., id, ...)
>insert into child_table ( ..., id, )

Which would make sequences unusable for parent/child tables under plain-vanilla SQL.

I know you can use variables in SQL*Plus now (v7.3 on ?), but that wasn't available when sequences were introduced (v6.0 ?). Received on Fri Apr 24 1998 - 02:24:09 CDT