| Oracle FAQ | Your Portal to the Oracle Knowledge Grid | |
 |
 | |||
| Oracle FAQ | Your Portal to the Oracle Knowledge Grid | |
|
| |||
Home -> Community -> Usenet -> c.d.o.server -> Re: Re: Re: Sequence Number Re-use is it possible, what is the standard
if a "bid" is a "bid" when you assign a unique number, and it is "tracked" in the system, then perhaps the unique number shouldn't be reused for another "bid".
it may be that your client is in need some education as to the purpose of a unique identifer. the number isn't unique if it's used again for another "bid".
an Oracle SEQUENCE is specifically designed to NOT return a duplicate number.
the SEQUENCE is implemented to meet the requirement for multiple processes to obtain unique numbers (usually used as identifiers), without having to serialize access to (i.e. obtain a lock on) a single database object.
if your system requirement is for something other than a unique number, then a SEQUENCE may not be the best option to meet that requirement.
if you insist on using a sequence, but also reusing the numbers, then you may find that you need to store the "returned" bid numbers in a "reuse pool" (implemented as a table). processes that need a number from the "reuse pool" will need to obtain an exclusive lock on a row in the table before deleting it from the "reuse pool".
you could try creating a table and a package to make this work. here is a rough outline, there are some areas that need improvement, like handling of oracle exceptions that may be raised (e.g. ORA-00054) but it's a start
CREATE TABLE bidno_reuse
(BIDNO NUMBER(38)
,CONSTRAINT bidno_reuse_pk
PRIMARY KEY (BIDNO)
);
CREATE OR REPLACE PACKAGE my_package IS
FUNCTION get_bidno RETURN NUMBER;
PROCEDURE return_bidno (ln_bidno IN NUMBER);
END my_package;
/
CREATE OR REPLACE PACKAGE BODY my_package IS
FUNCTION get_bidno RETURN NUMBER
IS
ln_bidno NUMBER(38);
CURSOR lcsr_reuse IS
SELECT r.ROWID, r.BIDNO
FROM bidno_reuse r
WHERE ROWNUM=1
FOR UPDATE NOWAIT;
lrec_reuse lcsr_reuse%ROWTYPE;
BEGIN
OPEN lcsr_reuse;
FETCH lcsr_reuse INTO lrec_reuse;
IF lcsr_reuse%NOTFOUND THEN
SELECT bidno_sequence.NEXTVAL
INTO ln_bidno
FROM DUAL WHERE ROWNUM=1;
PROCEDURE return_bidno (ln_bidno IN NUMBER)
IS
BEGIN
INSERT INTO bidno_reuse (BIDNO) VALUES (ln_bidno);
COMMIT;
END return_bidno;
END my_package;
/
from your application, get bid numbers using an anonymous PL/SQL block. if using Pro*C, for example, like this:
EXEC SQL EXECUTE
begin
:my_bidno := my_package.get_bidno;
end;
END-EXEC;
and return bid numbers to be reused like this:
EXEC SQL EXECUTE
begin
my_package.return_bidno(:my_bidno);
end;
END-EXEC;
<u28656005_at_spawnkill.ip-mobilphone.net> wrote in message
news:r.983804783.1236785888@[208.50.67.66]...
> Dear Spencer,
>
>> > until it is inserted into the table.
> >
> > have you considered postponing the assignment of the
> > unique sequence number until a row is actually inserted
> > into the table? i'm thinking that your "problem" could be
> > resolved with a small change to the application design.
> >
> > a "bid" isn't really a "bid" until it is "saved", so it doesn't
> > really make sense to acquire and assign a unique key
>
>
> > > > > > >
 |
 |