Home » Developer & Programmer » Forms » sequence is not generating number in sequence
sequence is not generating number in sequence [message #208709] Mon, 11 December 2006 23:28 Go to next message
pooja_09
Messages: 28
Registered: June 2005
Location: Delhi
Junior Member

hi frnds...
following code is working fine but it is not generating sequence in order.....like after P009....it is generating P021...so what should I do to solve it out...

BEGIN
go_block('product_master');
create_record;

item_enable_disable(property_on);
SELECT 'P' || LPAD(TO_CHAR(p_seq.NEXTVAL), 3, '0')
INTO :product_master.product_no
FROM dual;

set_item_property('product_master.product_no',insert_allowed,property_off);
set_item_property('product_master.product_no',update_allowed,property_off);

END;

Thanks in advance..
Re: sequence is not generating number in sequence [message #208716 is a reply to message #208709] Mon, 11 December 2006 23:51 Go to previous messageGo to next message
djmartin
Messages: 10180
Registered: March 2005
Location: Canberra ACT Australia
Senior Member
Account Moderator
This is the problem with using Oracle sequences in displayed keys. The sequence number has a cache value which by default is set to 20. You can set it to 1 by defining 'nocache' when you create the Oracle sequence. However, if you populate the field in the When-Create-Record trigger, using the 'nextval', and then decide not to save the record you will end up with a 'hole' in the sequence.

Oracle's Designer has two options for sequences - using 'nextval' or using 'max(field)+1'. I prefer to use a combination of the pair. I use a non-displayed field that contains the 'nextval' sequence which is used as the foreign key, etc and then a displayed key which is populated ONLY when the record is saved. This would be done in the Pre-Insert trigger in the form or in the 'insert' trigger in the database. This permits having really crappy displayed key structures while permitting internal efficiency in the database by only using a numeric field for the real key.

When the client WANTS a really crappy displayed key seriously consider having multiple key fields in the record so that data validation is possible. Then put them together via a function call for display purposes. There multiple fields can also be combined into a unique index for further validation purposes. This also permits a simpler process when THEY want to increase the size of one component from 2 to 3 characters because as the displayed field IS only a displayed field it is much easier to change.

David
Re: sequence is not generating number in sequence [message #208744 is a reply to message #208716] Tue, 12 December 2006 01:27 Go to previous messageGo to next message
pooja_09
Messages: 28
Registered: June 2005
Location: Delhi
Junior Member

thanks buddy....it is working...but is this a reliable technique to use..???

my sequence is :

create sequence p_seq
increment by 1
start with 1
nocache;

and one more thing...i didn't get ur previous post properly....so can u please give me one example...if possible..so that i can implement it also...

thanks in advance

Pooja
Re: sequence is not generating number in sequence [message #208986 is a reply to message #208744] Tue, 12 December 2006 19:40 Go to previous messageGo to next message
djmartin
Messages: 10180
Registered: March 2005
Location: Canberra ACT Australia
Senior Member
Account Moderator
I believe that the technique is reliable.

Let's say the user has a manual system which has a key '1/2006-A' which is actually three parts - the sequence within year and type, the year, and the type. But they want them to be sorted by type, then year, and then sequence. Also the sequence is to not have any 'holes'.

To do this with a single field 'sux' as the system has to decompose and recompose the field time after time after time. It makes the validation a pain and to get the next value typically requires processing the WHOLE table EVERY time. By having three indexed database fields containing the type, year, and number the calculation is much easier.

Sorting is also a lower cost exercise if the data is held in three fields.

You may or may not store the combined 'file id' field - your choice.

The data entry may be done into the three fields separately or into the type (dropdown list), with the year (dropdown list and defaulted), and the sequence automatically generated and displayed AFTER the record has been saved. Again three fields may be used for searching or a composite key accepted which is decomposed into the three fields and applied to the 'where' clause via either the 'default_where' or by populating the three fields in the Pre-Query trigger.

I hope that this has answered your query.

David

Upd: Corrected some difficult text.

[Updated on: Wed, 13 December 2006 00:35]

Report message to a moderator

Re: sequence is not generating number in sequence [message #209007 is a reply to message #208986] Tue, 12 December 2006 22:35 Go to previous message
pooja_09
Messages: 28
Registered: June 2005
Location: Delhi
Junior Member

Thanks a lot buddy..
Previous Topic: HOST function in forms 9i
Next Topic: what is the URL format for Forms10G?
Goto Forum:
  


Current Time: Fri Dec 09 00:08:57 CST 2016

Total time taken to generate the page: 0.13464 seconds