Re: Getting currval from a sequence with out incrementing nextval

Bonk wrote:
> Kind of a dumb question is there a good way in 10.2G to get the current
> value of a sequence number with first select thing the next value? Our
> issue is that we have a database that caches a large chunk of numbers
> on our sequences, and it appears that the column LAST_NUMBER on
> user_sequences (or dba_sequences) has the last value of the cached
> numbers.
>
> I would like to compare the current value of the sequence to the
> associated primary keys so that I can determine that I do not have a
> "unique key constraint" without burning a next value first.
>

Unless you use one sequence and some other way to insert numbers as primary keys, you *cannot* get a primary key violation.
That's why sequences were invented!

What's your business case anyway? Defensive programming is very OK with me, but having a table A(ID, COL1, COL2), and a trigger to populate ID from a sequence, and inserts like insert into A(col1, col2) values ('hello','there!') is quite OK.

There's no need to check for a PK violation in the first place, and if you still insist on handling one, do it where it belongs: in the exception handler of the trigger code, because it would be an exceptional sequence of events!

-- 
Regards,
Frank van Bortel

Top-posting is one way to shut me up...