hayub
asked on
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
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
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
But you can solve your real problem by recreating your sequence with NOCAHCE option
because a CACHED sequence usually causes such a gap.
Cheers
we used a trigger (no forms trigger), which populates a sequenced field on insert
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)+
end;
create or replace trigger ins_your_table
before insert
on
your_table
for each row
begin
select nvl(max(PK_SEQ_ID_COL),0)+
end;
"select nvl(max(PK_SEQ_ID_COL),0)+
ONLY if you are sure there would never be at least 2 users working in parallel within your screen !
ONLY if you are sure there would never be at least 2 users working in parallel within your screen !
In Forms I use Pre-Insert trigger for setting a sequence value to an item (after making all validation tests).
Post the code from the pre-insert trigger and maybe we can help.
tim
tim
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.
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.
ASKER
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
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
do not use it.
use PRE-INSERT trigger with
select my_seq.nextval
into <your field>
from dual;
use PRE-INSERT trigger with
select my_seq.nextval
into <your field>
from dual;
Usage of trigger is undoutfully more accurate because of the fact that the trigger is execute only when physical insertion occures
I agree with alexfrl, it is exactly what I do.
Exactly what I said :)
ASKER
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
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
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;
Begin
select spot_seq.nextval
into :block.item
from dual;
end;
ASKER CERTIFIED SOLUTION
membership
This solution is only available to members.
To access this solution, you must be a member of Experts Exchange.
ASKER
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
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
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
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
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
HTH
tim
ASKER
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
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
Which event (WHEN-NEW-RECORD or WHEN-NEW-RECORD-INSTANCE) is highly recommended to reference a value of sequence into item of the block?
by default oracle caches sequence numbers to improve performance. The NOCACHE option stops that.