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.

Current value of sequence you can obtain in this way:

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

Bonk wrote:
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...

The reason that I have this problem is simple, lazy developers.

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.

I was looking for a way to avoid bumping up the sequence by selecting
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

Bonk wrote:
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
damorgan@x.washington.edu
(replace x with u to respond)

Well, as I said - use an exception handler on the trigger.
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
--
Regards,
Frank van Bortel

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

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 wrote:
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
oracle_dba (AT) nospam (DOT) 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

Cgl3863 schrieb:
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