getting the currval of a sequence

On Fri, 14 Aug 1998, Fran Fabrizio wrote:

If you've recently done an insert and want to know what the value
was, you can do this:

select last_value from mysequence;

//
// Hi all,
//
// I need to do the equivalent of:
//
// select my_sequence.CURRVAL from Sys.dual;
//
// so I'm guessing its:
//
// select currvar('my_sequence') from ???????;
//
// Can anyone fill in the ???? Thanks!
//
// -Fran
//
//
//

--
SA, software.net My girlfriend asked me which one I like better.
pub 1024/3CAE01D5 1994/11/03 Dustin Sallings <dustin@spy.net>
| Key fingerprint = 87 02 57 08 02 D0 DA D6 C8 0F 3E 65 51 98 D8 BE
L_______________________ I hope the answer won't upset her. ____________

dustin sallings wrote:

On Fri, 14 Aug 1998, Fran Fabrizio wrote:

If you've recently done an insert and want to know what the value
was, you can do this:

select last_value from mysequence;

NO! This is wrong!

Use currval('_seq_name_') !!!

//
// Hi all,
//
// I need to do the equivalent of:
//
// select my_sequence.CURRVAL from Sys.dual;
//
// so I'm guessing its:
//
// select currvar('my_sequence') from ???????;
//
// Can anyone fill in the ???? Thanks!
//

Vadim

On Fri, 14 Aug 1998, Fran Fabrizio wrote:

If you've recently done an insert and want to know what the value
was, you can do this:

select last_value from mysequence;

Alternatively, you can get your key from the sequence before you insert, and
override the default value of the key (they will be the same only now you
explicitly got it). Now that you know what the key value use, after you
insert it you can do anything you want to with it. To do this just do:

select nextval ('sequencename');

...james

On Fri, 14 Aug 1998, Fran Fabrizio wrote:

If you've recently done an insert and want to know what the value
was, you can do this:

select last_value from mysequence;

Alternatively, you can get your key from the sequence before you
insert, and
override the default value of the key (they will be the same only now
you
explicitly got it). Now that you know what the key value use, after
you
insert it you can do anything you want to with it. To do this just
do:

select nextval ('sequencename');

...james

I think you should just go with what Vadim suggested and use:
select currval('my_sequence');
This will be the same within a transaction, even if there is another
transaction using the same sequence.
But (if memory servers), you have to have had select a nextval from that
same sequence within the transaction.

Hope this clears things up.
-DEJ

I think you should just go with what Vadim suggested and use:
select currval('my_sequence');
This will be the same within a transaction, even if there is another
transaction using the same sequence.

Sorry, I wasn't thinking of it happening in the same transaction. In that
case that would be the thing to do. If on the other hand you needed that
number over multiple transactions, wouldn't what I had suggested work?

...james

I think you should just go with what Vadim suggested and use:
select currval('my_sequence');
This will be the same within a transaction, even if there is another
transaction using the same sequence.

Sorry, I wasn't thinking of it happening in the same transaction. In
that
case that would be the thing to do. If on the other hand you needed
that
number over multiple transactions, wouldn't what I had suggested work?

...james

Yes it would, but your suggestion introduces problems of it's own. The
whole point of a transaction is encapsulate a logical unit of work, such
that it all succeeds together or it all fails together. This would
include the incrementing of the sequence. The way that you are
suggestion you would always increment the sequence even if you never
used it, it just seems like a waste to me.
-DEJ

I think you should just go with what Vadim suggested and use:
select currval('my_sequence');
This will be the same within a transaction, even if there is another
transaction using the same sequence.

Sorry, I wasn't thinking of it happening in the same transaction. In
that
case that would be the thing to do. If on the other hand you needed
that
number over multiple transactions, wouldn't what I had suggested work?

...james

Yes it would, but your suggestion introduces problems of it's own. The
whole point of a transaction is encapsulate a logical unit of work, such
that it all succeeds together or it all fails together. This would
include the incrementing of the sequence. The way that you are
suggestion you would always increment the sequence even if you never
used it, it just seems like a waste to me

Yikes, I didn't think of that. I suppose with 4 billion sequence numbers
you could waste a few, but...things always seem grow beyon their intended
purposes. I guess your absolutely right on this one...james

Jackson, DeJuan wrote:

I think you should just go with what Vadim suggested and use:
select currval('my_sequence');
This will be the same within a transaction, even if there is another

^^^^^^^^^^^
session

transaction using the same sequence.
But (if memory servers), you have to have had select a nextval from that
same sequence within the transaction.

^^^^^^^^^^^
session!

This is from create_sequence manual:

"...Function currval ('sequence_name') may be used
to determine number returned by last call to nextval for
specified sequence in current session."

Vadim

Jackson, DeJuan wrote:

I think you should just go with what Vadim suggested and use:
select currval('my_sequence');
This will be the same within a transaction, even if there is another

^^^^^^^^^^^
session

transaction using the same sequence.
But (if memory servers), you have to have had select a nextval from

that

same sequence within the transaction.

^^^^^^^^^^^
session!

This is from create_sequence manual:

"...Function currval ('sequence_name') may be used
to determine number returned by last call to nextval for
specified sequence in current session."

Vadim

Vadim, what's a session?
-DEJ