Sequence Number Generation in Forms Builder 6i

Hi Folks,

          I'm using sequence number in one of my forms which is basically a master-detail one and the sequence number field is the primary key. I have created the sequence number in the databse with the increment of 1. I'm using the initial property of the field so that the every time a new record is inserted, a new sequence number is generated. My question is that, I  only want a new sequence number to be generated when the user clicks on the insert record button. In this particular case, when users only run query, the sequence numbers are generated and when they actually insert a record then at least 6 numbers are missing.

Any ideas??


TIA


Hassan
hayubAsked:
Who is Participating?
I wear a lot of hats...

"The solutions and answers provided on Experts Exchange have been extremely helpful to me over the last few years. I wear a lot of hats - Developer, Database Administrator, Help Desk, etc., so I know a lot of things but not a lot about one thing. Experts Exchange gives me answers from people who do know a lot about one thing, in a easy to use platform." -Todd S.

oratimCommented:
Make sure you use NOCACHE when creating the sequence, otherwise you will skip numbers anyway.

by default oracle caches sequence numbers to improve performance. The NOCACHE option stops that.

0
alexfrlCommented:
Initial value is always generated on WHEN-RECORD-CREATED event therefore this theory doesn't work

But you can solve your real problem by recreating your sequence with NOCAHCE option
because a CACHED sequence usually causes such a gap.

Cheers
0
kretzschmarCommented:
we used a trigger (no forms trigger), which populates a sequenced field on insert
0
Ultimate Tool Kit for Technology Solution Provider

Broken down into practical pointers and step-by-step instructions, the IT Service Excellence Tool Kit delivers expert advice for technology solution providers. Get your free copy now.

oratimCommented:
A trigger is an option, and you can avaoid using a sequence entirely if you do something like this in the trigger:

create or replace trigger ins_your_table
before insert
on
your_table
for each row
begin

select nvl(max(PK_SEQ_ID_COL),0)+1 into :new.PK_SEQ_ID_COL from your_table;

end;

0
alexfrlCommented:
"select nvl(max(PK_SEQ_ID_COL),0)+1 into :new.PK_SEQ_ID_COL from your_table;"

ONLY if you are sure there would never be at least 2 users working in parallel within your screen !
0
Helena Markováprogrammer-analystCommented:
In Forms I use Pre-Insert trigger for setting a sequence value to an item (after making all validation tests).
0
oratimCommented:
Post the code from the pre-insert trigger and maybe we can help.

tim
0
pratikroyCommented:
Well, it depends on how you have coded your form triggers. What is the code in the WHEN-BUTTON-PRESSED of your INSERT button. I believe it should just have something like COMMIT_FORM. You should keep your Sequence Generator statement only in the PRE-INSERT trigger - something like :
select s_next_id.nextval
into :data_blk.id
from dual;

If you are loosing out the 6 numbers every time, that means that the sequence is generated 6 times before the actual insert happens. That is the reason, it should be generated only in the PRE-INSERT. So, you will have to check each trigger that you have coded for - in your form.
0
hayubAuthor Commented:
Hi Folks,

  I used the NOCACHE option while creating the sequence and now the gap in the sequence comes to 2 instead of 1 which is I guess acceptable, but every time I run a query in the forms a new sequence number is generated and since I'm not inserting a new record, this particular number is obviously wasted. Can I avoid this? I'm using the initial value property of the item on which I'm using sequence number by writing :sequence.my_seq.nextval.

                    If I use a trigger for the sequence generation will that be more accurate?


TIA

Hassan
0
alexfrlCommented:
do not use it.

use PRE-INSERT trigger with

select my_seq.nextval
  into <your field>
  from dual;

0
alexfrlCommented:
Usage of trigger is undoutfully more accurate because of the fact that the trigger is execute only when physical insertion occures
0
Helena Markováprogrammer-analystCommented:
I agree with alexfrl, it is exactly what I do.
0
pratikroyCommented:
Exactly what I said :)
0
hayubAuthor Commented:
Hi Folks,

     When you are talking about the trigger is it the database trigger or a form trigger? I tried the form trigger at the form,block and item level but it does not populate the field with the sequence number, although the code compiles without any errrors. Following is the text of the code whih I used:

     declare
              a number;
      begin
     select spot_seq.nextval
     into a
      from dual;
       insert into ledger(lno)
       values(a);
end;


Any ideas? or am I missing some basic stuff here....


TIA

Hassan
0
Helena Markováprogrammer-analystCommented:
We have speak about form PRE-INSERT trigger. You have to modify your code, because it does not set block item.

Begin
    select spot_seq.nextval
     into :block.item
     from dual;
end;
0
oratimCommented:
you need to have a block item on the form that references the column lno in the ledger table

ie

Begin
    select spot_seq.nextval
     into :ledger_block.lno
     from dual;
end;

tim
0

Experts Exchange Solution brought to you by

Your issues matter to us.

Facing a tech roadblock? Get the help and guidance you need from experienced professionals who care. Ask your question anytime, anywhere, with no hassle.

Start your 7-day free trial
hayubAuthor Commented:
Hi,

   What should be the level of this trigger? form level,block level or item level? I tried on the form and item level it does not work and works on the block level but keeps on generatiing numbers without even inserting records.....



hassan
0
alexfrlCommented:
Hasan,

check yourselves again.
1) The trigger works on Save or on Post inserted row.
2) The trigger always works on Item level - if you do not commit or makes rollback there are no rows created (inserted) in the Database
3) The trigger may not work on Form/Block level if the higher priority level trigger defined on Execution Hierarchy=Override
    the priorities are (Low->High) Forms->Block->Item
4) In your case 2 or 3 triggers works - the same triggers which you probably placed one by one on Block, Item and Form levels and therefore the sequence always jumps 2,3 times
0
oratimCommented:
I tested it with a trigger at both the form and block levels and everything worked fine. Check all the code, and maybe try to step thru it one lne at a time and figure out what exactly is being done behind the scenes.

HTH
tim
0
hayubAuthor Commented:
Thanks Tim,

   It worked on the insert record button which I created in the same data block and every time I press the button to insert record, a new number is generated. The button has simply the create_record built in so I just added your mentioned code after that and it worked.

regards

Hassan
0
adimurtyCommented:
Which event (WHEN-NEW-RECORD or WHEN-NEW-RECORD-INSTANCE) is highly recommended to reference a value of sequence into item of the block?
0
It's more than this solution.Get answers and train to solve all your tech problems - anytime, anywhere.Try it for free Edge Out The Competitionfor your dream job with proven skills and certifications.Get started today Stand Outas the employee with proven skills.Start learning today for free Move Your Career Forwardwith certification training in the latest technologies.Start your trial today
Oracle Database

From novice to tech pro — start learning today.

Question has a verified solution.

Are you are experiencing a similar issue? Get a personalized answer when you ask a related question.

Have a better answer? Share it in a comment.