Home » Developer & Programmer » Forms » auto generating a serial number on a form's multi record block
auto generating a serial number on a form's multi record block [message #304388] Wed, 05 March 2008 01:01 Go to next message
athar.fitfd@hotmail.com
Messages: 193
Registered: October 2007
Location: pakistan
Senior Member
Hi every one on the forum.
I want to create a serial number on my form .
i want to create multiple records before saving them. and the serial no should be increased by one when i create the next record before saving the previous record.

The following query works well when i save record before creating any further record.

select nvl(max(sr_no),0)+1 into :sr_no from table_name;

but i want to show this effect before saving any record.

i hope you genius people understand my problem.

i will be very thankful to you for any idea or solution.
Re: auto generating a serial number on a form's multi record block [message #304393 is a reply to message #304388] Wed, 05 March 2008 01:05 Go to previous messageGo to next message
azamkhan
Messages: 557
Registered: August 2005
Senior Member
Dear Friend,

Tell me is your application is running for Network environment and will involve more then one users at a time performing DML operations through your application.

Regards,
Azam Khan
Re: auto generating a serial number on a form's multi record block [message #304397 is a reply to message #304393] Wed, 05 March 2008 01:10 Go to previous messageGo to next message
athar.fitfd@hotmail.com
Messages: 193
Registered: October 2007
Location: pakistan
Senior Member
My Application is running on a network environment
and can be used by more one end users.
Re: auto generating a serial number on a form's multi record block [message #304400 is a reply to message #304388] Wed, 05 March 2008 01:20 Go to previous messageGo to next message
Littlefoot
Messages: 21823
Registered: June 2005
Location: Croatia, Europe
Senior Member
Account Moderator
Although MAX + 1 might work in a single-user environment, it will fail in multi-user one; sooner or later two users will fetch the same number and - if there was a primary key (or other kind of uniqueness checking) - it would raise DUP-VAL-ON-INDEX.

The correct way is to use a sequence. Create it in in user's schema as
CREATE SEQUENCE my_seq;
. In form, create a block-level WHEN-NEW-RECORD-INSTANCE trigger as
SELECT my_seq.nextval INTO :block.sequence_item;
Re: auto generating a serial number on a form's multi record block [message #304401 is a reply to message #304397] Wed, 05 March 2008 01:20 Go to previous messageGo to next message
azamkhan
Messages: 557
Registered: August 2005
Senior Member
athar.fitfd@hotmail.com wrote on Wed, 05 March 2008 01:10
My Application is running on a network environment
and can be used by more one end users.


Dear if will be better not to show the actual serial number b/c your appli is being used by more then one users at a time there is a possiblility that more then one user will see in correct serial number b/c every one is picking the serial number from a table which is showing every one the number which might be used by another user. I hope you got my point.

Ok tell me why are you saving the serial numbers. Did you used it as a PK or for some other purpose.

Why dont you generate the serial number on temperory basis. I mean just generate the number on runtime for each user. In this way every one will see correct serial number. And if two users are performing insert or delete on this table then both user will see correct number when they re-query the records after saving.

Regards,
Azam Khan
Re: auto generating a serial number on a form's multi record block [message #304403 is a reply to message #304400] Wed, 05 March 2008 01:22 Go to previous messageGo to next message
azamkhan
Messages: 557
Registered: August 2005
Senior Member
There is a problem in generating Sequence number when you have generate a sequence number it is generated. if a user enter records the SEQUENCE is generated but suppose if the user didn't save the transaction then what? Your will have unwanted SEQUENCE numbers.

Regards,
Azam Khan
Re: auto generating a serial number on a form's multi record block [message #304409 is a reply to message #304403] Wed, 05 March 2008 01:37 Go to previous messageGo to next message
Littlefoot
Messages: 21823
Registered: June 2005
Location: Croatia, Europe
Senior Member
Account Moderator
azamkhan
There is a problem in generating Sequence number when you have generate a sequence number it is generated.

/forum/fa/1600/0/

azamkhan
if a user enter records the SEQUENCE is generated but suppose if the user didn't save the transaction then what? Your will have unwanted SEQUENCE numbers.
So what? Sequence will still ensure there are unique numbers in that column. Gaps? Who cares about gaps?
Re: auto generating a serial number on a form's multi record block [message #304410 is a reply to message #304388] Wed, 05 March 2008 01:39 Go to previous messageGo to next message
athar.fitfd@hotmail.com
Messages: 193
Registered: October 2007
Location: pakistan
Senior Member
Suppose , my application is used by only one person.
then what will be the possibility.
Re: auto generating a serial number on a form's multi record block [message #304411 is a reply to message #304409] Wed, 05 March 2008 01:44 Go to previous messageGo to next message
azamkhan
Messages: 557
Registered: August 2005
Senior Member
Dear Littlefoot he is generating serial number and what I think that serial number must be in sequence that why I think gaps are important for him

Regards,
Azam Khan
Re: auto generating a serial number on a form's multi record block [message #304413 is a reply to message #304410] Wed, 05 March 2008 01:47 Go to previous messageGo to next message
azamkhan
Messages: 557
Registered: August 2005
Senior Member
Dear if your application is running for single user environment then serial numbers will not have gaps. Because only one user is using your application. If he pick a highest serial number then it will be the same number until he save the records. Because no other user is getting and updating the serial number so the highest serial number will be the same.

Regards,
Azam Khan
Re: auto generating a serial number on a form's multi record block [message #304418 is a reply to message #304388] Wed, 05 March 2008 01:59 Go to previous messageGo to next message
athar.fitfd@hotmail.com
Messages: 193
Registered: October 2007
Location: pakistan
Senior Member
Problem is still there, because i dont want to user the sequence

Regards
Athar
Re: auto generating a serial number on a form's multi record block [message #304428 is a reply to message #304388] Wed, 05 March 2008 03:09 Go to previous messageGo to next message
mshrkshl
Messages: 247
Registered: September 2006
Location: New Delhi
Senior Member
use pre insert trigger on table_name object(data block level).

and trigger code like:-

select nvl(max(sr_no),0)+1 into :sr_no from table_name;
:table_name.sr_no := :sr_no;


regards,

[Updated on: Wed, 05 March 2008 03:10]

Report message to a moderator

Re: auto generating a serial number on a form's multi record block [message #304433 is a reply to message #304388] Wed, 05 March 2008 03:24 Go to previous messageGo to next message
athar.fitfd@hotmail.com
Messages: 193
Registered: October 2007
Location: pakistan
Senior Member
My dear, i want to show it on the form before any action of saving the record.



Athar
Re: auto generating a serial number on a form's multi record block [message #304437 is a reply to message #304388] Wed, 05 March 2008 03:41 Go to previous messageGo to next message
mshrkshl
Messages: 247
Registered: September 2006
Location: New Delhi
Senior Member
oh no! how is it possible?

regards,
Re: auto generating a serial number on a form's multi record block [message #304460 is a reply to message #304388] Wed, 05 March 2008 05:17 Go to previous message
athar.fitfd@hotmail.com
Messages: 193
Registered: October 2007
Location: pakistan
Senior Member
IT IS POSSIBLE






BY USING

:SYSTEM.CURSOR_RECORD;
Previous Topic: date
Next Topic: link
Goto Forum:
  


Current Time: Sat Feb 15 06:37:24 CST 2025