OraFAQ Forum: Forms » Avoiding Gaps in Sequence Number Inputs

Home » Developer & Programmer » Forms » Avoiding Gaps in Sequence Number Inputs

Show: Today's Messages :: Polls :: Message Navigator
E-mail to friend

Avoiding Gaps in Sequence Number Inputs [message #131141]

Thu, 04 August 2005 10:34

sgirl
Messages: 5
Registered: August 2005
Location: USA

Junior Member

Hi!

I am newbie to coding in Oracle forms. I am developing multi application. Some blocks contain an id item that should be populated into the DB table by a previously defined sequence. I have set the Initial Value property of this item as

:SEQUENCE.my_sequence_name.NEXTVAL.

This helps in generating the sequence number, but it is introducing gaps in the that particular column values, as the sequence.nextval gets incremented each time a next button is pressed and irrespective of whether that record is committed or not.

How do I use the Oracle sequences in FORMS so that I do not have any gaps in my column values? It is important in my business process that I do not have gaps.

I tried searching on google and this forum for this issue, but no go!!

I will greatly appreciate your expert advice!

Thanks,

SGirl

Report message to a moderator

Re: Avoiding Gaps in Sequence Number Inputs [message #131193 is a reply to message #131141]

Thu, 04 August 2005 16:20

hamdard
Messages: 37
Registered: July 2005

Member

Hi,
Sequence number should be inserted when we want to create a new record. Even this way we'r not sure that we'll not have the gaps in the generated numbers. I have one thing in my mind if you can try it.
Create a table in which you can store that sequence number which the user is wasting.
CREATE TABLE waisted_seq_numbers
(seq_no_waisted NUMBER);

Then while inserting the record insert the sequence number in your forms field and also insert this number in the waisted_seq_numbers table as well. Then if the user saves the record, delete the sequence number in the waisted_seq_numbers table otherwise if the user does not save that record for which the sequence number has been generated then save that number in the waisted_seq_numbers table. Next time befor generating the sequence number, check if seq_no_waisted exists in the table or not. If it exists populate that number in your form's field instead of generating new sequence number. If that table is empty, then only generate a new sequence number.
Give it a try and I hope it should work.

Regards.

Report message to a moderator

Re: Avoiding Gaps in Sequence Number Inputs [message #131210 is a reply to message #131193]

Thu, 04 August 2005 18:45

djmartin
Messages: 10181
Registered: March 2005
Location: Surges Bay TAS Australia

Senior Member
Account Moderator

There two other alternatives (always more alternatives).

Populate your sequence field in a trigger in the database.

IF :new.seq is null then
  :new.seq := :SEQUENCE.my_sequence_name.NEXTVAL;
end if;

OR use the Oracle Designer alternate of

IF :new.seq is null then
  select max(seq)+1
    into :new.seq
    from my_table; 
end if;

Hope these help.

David

Report message to a moderator

Re: Avoiding Gaps in Sequence Number Inputs [message #131336 is a reply to message #131193]

Fri, 05 August 2005 07:18

sgirl
Messages: 5
Registered: August 2005
Location: USA

Junior Member

Hi Hamdard,

Just saw the solution that u sent. It does look like workable solution. Will try it and keep u posted on how that went.

Thanks for your time!

Suma

Report message to a moderator