Getting currval from a sequence with out incrementing nextval
Bonk
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.
Martin...@gmail.com
DECLARE
curr_val NUMBER;
BEGIN
SELECT my_seq.CURRVAL
INTO curr_val
FROM DUAL;
END;
be carefull, if SELECT MY_SEQ.NEXTVAL FROM DUAL; has never been
perfomed before you will get this error:
ORA-08002: sequence MY_SEQ.CURRVAL is not yet defined in this session
But i would prefer to leave checking of unique constraint on Oracle.
What if your check on id 100 passed, but after that moment someone
insert 100 too, and you than insert 100 again.
Martin
Frank van Bortel
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...
Bonk
It seems that with every new release of our application they like to
run a scripts that drops all the sequences, then re-adds them to the
system starting at zero. Granting I can cross reference all the tables
and locate which sequences need to be bumped up, by the PK on the
effected tables, but alas I am lazy. I'm trying to write a sanity check
plsql that will, allow me to verify that we won't be in a "unique key
constraint" situation with every build or speed up the fix at the very
least. Secondly, I thought it might be nice to check periodically in
our QA environment as they are constantly breaking that environment.I
want to get out of that loop and if I can hand them a good tool there
will be less work for me.
I know I can select the nextval, but I was looking to see if there was
a way to get the currval without losing a sequence number. Granted
burning values in our "soon to be" production environment is not a big
deal as the range is huge but in QA they are limited to a couple
thousand and real estate is a bit limited. Ultimately I guess it
doesn't really matter, cause I am so annoyed that I am going to write
something.
Bonk
the next value, I thought there might be a $ table that contained the
values, but I guess not. I don't see see it in any of the DBA/SQL
references for 10G
DA Morgan
You are looking for a way to make your application unscalable to cover
up for organizational incompetence.
Fix the real problem.
--
Daniel A. Morgan
http://www.psoug.org
damo...@x.washington.edu
(replace x with u to respond)
Frank van Bortel
It can call a procedure which can can have going
over a loop, say 100 times before raising an error.
But the proverbial lead pipe does wonders, here.
Oh - and have them test without cached sequences
(nocache noorder nocycle) - so you can query dba_sequences ;)
Cgl3863
Typical of DBA's!!!: over complicating a simple question
if you want the to find the current number of a given Sequence
SELECT last_number FROM ALL_SEQUENCES Where Sequence_name='sequence
name'
from lazy developer
--
Cgl3863
------------------------------------------------------------------------
Cgl3863's Profile: http://www.dbtalk.net/m261
View this thread: http://www.dbtalk.net/t289463
Brian Peasland
> Typical of DBA's!!!: over complicating a simple question
> if you want the to find the current number of a given Sequence
>
> SELECT last_number FROM ALL_SEQUENCES Where Sequence_name='sequence
> name'
>
> from lazy developer
>
>
Typical of developers to think they know more than the DBA...and too
lazy to read the docs....
The LAST_NUMBER column from ALL_SEQUENCES (straight from the docs) is
defined as:
"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."
Take special that the number is "likely to be greater than the last
sequence number that was used".
Cheers!
Brian
--
===================================================================
Brian Peasland
oracl...@nospam.peasland.net
http://www.peasland.net
Remove the "nospam." from the email address to email me.
"I can give it to you cheap, quick, and good.
Now pick two out of the three" - Unknown
Maxim Demenko
> Typical of DBA's!!!: over complicating a simple question
> if you want the to find the current number of a given Sequence
>
> SELECT last_number FROM ALL_SEQUENCES Where Sequence_name='sequence
> name'
>
> from lazy developer
>
>
Typical of *lazy* developers, to give a wrong followup to a correct
answer ...
PS. ( and yes , you can try to compare the numbers after
create sequence s;
select s.nextval from dual;
select s.currval from dual;
select last_number FROM ALL_SEQUENCES Where Sequence_name='S';
)
Best regards
Maxim
Brian Peasland
> select s.nextval from dual;
> select s.currval from dual;
> select last_number FROM ALL_SEQUENCES Where Sequence_name='S';
And just in case the lazy one is too lazy to perform a simple test, I
post the results here:
SQL> CREATE SEQUENCE s;
Sequence created.
SQL> SELECT s.NEXTVAL FROM dual;
NEXTVAL
----------
1
SQL> SELECT s.CURRVAL FROM dual;
CURRVAL
----------
1
SQL> SELECT last_number FROM all_sequences WHERE sequence_name='S';
LAST_NUMBER
-----------
21
Cheers,