?
Solved

Sequence Number Generation in Forms Builder 6i

Posted on 2004-11-29
21
Medium Priority
?
3,562 Views
Last Modified: 2011-09-20
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
0
Comment
Question by:hayub
  • 5
  • 5
  • 4
  • +4
21 Comments
 
LVL 3

Expert Comment

by:oratim
ID: 12696191
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
 
LVL 5

Expert Comment

by:alexfrl
ID: 12696194
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
 
LVL 27

Expert Comment

by:kretzschmar
ID: 12696212
we used a trigger (no forms trigger), which populates a sequenced field on insert
0
VIDEO: THE CONCERTO CLOUD FOR HEALTHCARE

Modern healthcare requires a modern cloud. View this brief video to understand how the Concerto Cloud for Healthcare can help your organization.

 
LVL 3

Expert Comment

by:oratim
ID: 12696369
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
 
LVL 5

Expert Comment

by:alexfrl
ID: 12696425
"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
 
LVL 22

Expert Comment

by:Helena Marková
ID: 12703267
In Forms I use Pre-Insert trigger for setting a sequence value to an item (after making all validation tests).
0
 
LVL 3

Expert Comment

by:oratim
ID: 12704540
Post the code from the pre-insert trigger and maybe we can help.

tim
0
 
LVL 9

Expert Comment

by:pratikroy
ID: 12716555
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
 

Author Comment

by:hayub
ID: 12717933
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
 
LVL 5

Expert Comment

by:alexfrl
ID: 12717954
do not use it.

use PRE-INSERT trigger with

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

0
 
LVL 5

Expert Comment

by:alexfrl
ID: 12718013
Usage of trigger is undoutfully more accurate because of the fact that the trigger is execute only when physical insertion occures
0
 
LVL 22

Expert Comment

by:Helena Marková
ID: 12723365
I agree with alexfrl, it is exactly what I do.
0
 
LVL 9

Expert Comment

by:pratikroy
ID: 12723806
Exactly what I said :)
0
 

Author Comment

by:hayub
ID: 12725147
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
 
LVL 22

Expert Comment

by:Helena Marková
ID: 12725203
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
 
LVL 3

Accepted Solution

by:
oratim earned 600 total points
ID: 12725227
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
 

Author Comment

by:hayub
ID: 12726853
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
 
LVL 5

Expert Comment

by:alexfrl
ID: 12727072
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
 
LVL 3

Expert Comment

by:oratim
ID: 12728446
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
 

Author Comment

by:hayub
ID: 12735950
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
 

Expert Comment

by:adimurty
ID: 12881549
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

Featured Post

Restore individual SQL databases with ease

Veeam Explorer for Microsoft SQL Server delivers an easy-to-use, wizard-driven interface for restoring your databases from a backup. No expert SQL background required. Web interface provides a complete view of all available SQL databases to simplify the recovery of lost database

Question has a verified solution.

If you are experiencing a similar issue, please ask a related question

Checking the Alert Log in AWS RDS Oracle can be a pain through their user interface.  I made a script to download the Alert Log, look for errors, and email me the trace files.  In this article I'll describe what I did and share my script.
Shell script to create broker configuration file using current broker Configuration, solely for purpose of backup on Linux. Script may need to be modified depending on OS-installation. Please deploy and verify the script in a test environment.
This video shows how to set up a shell script to accept a positional parameter when called, pass that to a SQL script, accept the output from the statement back and then manipulate it in the Shell.
This video shows syntax for various backup options while discussing how the different basic backup types work.  It explains how to take full backups, incremental level 0 backups, incremental level 1 backups in both differential and cumulative mode a…

862 members asked questions and received personalized solutions in the past 7 days.

Join the community of 500,000 technology professionals and ask your questions.

Join & Ask a Question