Re: Sequence without holes

P B wrote:

> Question #1
>
> I would like to have a sequence but as I understand it, if for some
> reason a user make a rollback or what so ever, I loose his sequence
> number.
>
> I don't want that. I want a sequence that has no hole, so if a user
> make a rollback, the next user that request a number would receive the
> number that was rolled back.
>
> Question #2
>
> How do I lock a row that does not exist? I did not yet insert the row
> but I will soon and I don't want other user to use my key. I dont want
> to lock the whole table because I'm not the only who use it.

It is very rare that what you are asking for is a requirement rather than just a nicety. But assuming it is a requirement.

CREATE OR REPLACE FUNCTION get_seqno RETURNS PLS_INTEGER IS

PRAGMA AUTONOMOUS_TRANSACTION; i PLS_INTEGER;

BEGIN
   SELECT sequence_name.NEXTVAL
   INTO i
   FROM dual;

   INSERT INTO transaction_log_table
   (trans_seq_no, date_time)
   VALUES
   (i, SYSDATE);
   COMMIT;    RETURN i

EXCEPTION
   WHEN OTHERS THEN
      RETURN 0;
END get_seqno;
/

Now none are lost. They may not be used ... but they aren't lost.

Another function or proc can take care of logging into additional fields in the transaction_log_table
the Oracle Error corresponding to the lost numbers. And the sequence must be created without
caching.

Daniel Morgan Received on Fri Oct 18 2002 - 11:10:20 CDT