Re: Sequences?

From: Eric Bowman <ebowman_at_systec.com>
Date: 17 Feb 2002 05:19:27 -0800
Message-ID: <3a1c1b63.0202170519.2d5427b3@posting.google.com>

I had a particular requirement to have an autonumber field which is the 3rd field of the primary key and would reset for each new record in the first 2 fields. Primary key is alarm_year, alarm_num, seq_num. seq_num starts at 000 and increments by 1 each time duplicate alarm_year and alarm_num are inserted. Additionally, they wanted the option to delete the last assigned record and have the seq_num re-used. Records look like this:

year alarmnum seq_num

2001, 0000001, 000
2001, 0000002, 000
2001, 0000001, 001
2001, 0000001, 002
2001, 0000003, 000

The year and alarm_num are provided to the stored procedure by the app and I get the seq_num from function call before insert. All 3 fields in are varchar2 for this app but you can remove the to_char(to_num) stuff if you just want to do it with a true number field.

I found a function was an easier solution than a sequence for this. The only possible problem I can think of is if 2 users happen to call this function at exactly the same time they will receive the same seq_num. There are not many users on this system so it is extremely unlikely but the worst that happens in this case the second user will receive a primary key violation error when trying to insert. I just trap this in the app, delay a second and repeat the call. Function is below. You can easily modify it to handle a single field primary key. Hope this helps.

--This fn returns the next seqNum based on the year and alarm_num --Primary key for alarm_hdr is year, alarm_num, seq_num function next_seq_num

          (strYear  varchar2,
          strAlarmNum varchar2)

return varchar2 is
  strLastSeqNum varchar2(7);
  strSeqNum varchar2(7);
  cursor curNextSeq is

         select max(seq_num)
         from alarm_hdr
         where alarm_year = strYear 
         and alarm_num = strAlarmNum;

begin
  open curNextSeq;
  fetch curNextSeq into strLastSeqNum;
  close curNextSeq;
  if strLastSeqNum is null then
    strSeqNum := '000';
  else
    strSeqNum := lpad(to_char(to_number(strLastSeqNum)+1),3,'0');   end if;
return(strSeqNum);
end next_seq_num;

Eric Bowman
ebowman_at_systec.com

"Heinz Kiosk" <no.spam_at_ntlworld.com> wrote in message news:<Ufrb8.43294$YA2.6166787_at_news11-gui.server.ntli.net>...
> FYI, although I am not touting MS Access as a rival platform to Oracle ;), I
> have found MS Access Autonumbers to be extremely fast, efficient, intuitive,
> and transaction-safe, just like the identity columns in many server based
> SQL dbs.
>
> I've recently ported a large app to Oracle. To get the behaviour out of
> Oracle which is simply a column-attribute in other dbs I've had to create a
> load of sequences, and a matching set of triggers which check for incoming
> values, and use the sequence if none is supplied (sometimes you want to
> supply the value yourself, eg on a mass data upload), other dbs ignore the
> autonumber/identity if you supply one, moreover if the supplied value is
> greater than the current stored identity value the sequence gets
> automatically reset to max+1 for you. This cannot be achieved in Oracle so
> I've had to write an out-of-process SP which checks all the sequences
> against max(theircolvalue) and "create and replaces" the sequence with the
> new highest number if necessary. Someone has to remember to run this SP when
> necessary. Quite a lot of pain to achieve an effect that I get for free in
> every other db.
>
> To promote the lack of autonumber/identity as an advantage of Oracle's
> superior technology as some have done is a ludicrous kind of "Oracle does
> it that way so it must be the best" kind of thinking. Religious.
>
> Regards
Received on Sun Feb 17 2002 - 07:19:27 CST