Re: Need Help With SQL*Forms and Nextval

mickel_at_OES.ORST.EDU (Paul Mickel) writes:
>We had attempted to use sequence numbers in one of our forms to assign run
>numbers to a production process. The form we were using was a 2-block form.
>What we found was that the the next run number generate was three more than
>the last, with the pattern in increment being (#block_on_form+1). We couldn't
>find a work around and had to abandon the use of sequences.
>-Paul
>--
>Paul M. Mickel Internet:mickel_at_oes.orst.edu
>Database Programmer, Teledyne Wah Chang Albany, OR 97321
>Disclaimer: My employer never claims my opinions (unless it makes a profit).

Oracle in their documentation of the SEQUENCE construct explicitly mentions NOT to expect guaranteed by-1 sequence numbers under all circumstances with no missing values. The SEQUENCE is used SOLELY to provide a guaranteed transaction-unique number.

Couple cases:

A cached sequence (the default) pre-allocates groups of sequences and stores the next-value back into the data dictionary. If the database shuts down and restarts, the next set of values start with the stored value, discarding the pre-allocated values.

Using FORMS 3.0 with :sequence.owner.{sequence_name} as the field default value means that EVERY time the block is entered with a status of 'NEW' record (ie, for every new record) a new sequence number value is generated and stored in the column, with the record status left as 'NEW'.

One way to get near-by-1 sequence numbers is to set up an ON-VALIDATE-FIELD trigger on the sequenced field, which selects the next sequence number into the field where its current value is null:

   SELECT Sequence_name.Nextval into :Myfield    FROM DUAL
   WHERE :Myfield is null;

Another way, and the oracle recommended way, is to create a table which contains your by-1 sequences:

  create table by1 ( seq_name char(30) not null, val number not null);   insert into by1 values ('SEQUENCE1',1);

Now, in your oracle PRE-INSERT trigger for the record add these statements:
  LOCK by1;
  select val into :myfield from by1 where seq_name='SEQUENCE1'   FOR UPDATE OF val;
  update val set val=val+1 where seq_name='SEQUENCE1'; /* UNLOCK by1; */

I'm not sure if the UNLOCK statement is necessary. Some versions of forms it was, some it was not.

Hope that helps.

Andy

-- 
Andrew Finkenstadt  |  andy_at_{homes.com,vistachrome.com,genie.geis.com}
Systems Analyst     |  Vista-Chrome, Homes & Land Publishing Corporation
                    |  1600 Capital Circle SW, Tallahassee Florida 32310
+1 904-575-0189     |  GEnie Postmaster, Unix & Internet RoundTables Sysop