| for update [message #372331] |
Fri, 02 February 2001 14:38  |
Bobby
Messages: 32
Registered: August 2000
|
Member |
|
|
|
I want to generate an order number without using a sequence. The order number generated should be +1 of the previous order number. Plz give me the query thats works in multi user platform so that the order number generated is unique.
|
|
|
|
| Re: for update [message #372333 is a reply to message #372331] |
Fri, 02 February 2001 15:20  |
Andrew again...
Messages: 270
Registered: July 2000
|
Senior Member |
|
|
insert into abc
(select nvl(max(ID), 0)+1, 999, 'value 2', sysdate
from abc)
where ID is the primary key. you just replace the hardcoded values with your variable names. The NVL is to cater for the first ever record.
Another way to avoid sequence number generators is to maintain a table with last used PK's in it. e.g.
table_name, last_pk
---------- -------
EMP 923
DEPT 41
The problem with this is that this table gets hit hard as every insert statement needs to update the appropriate row in this table. It also becomes difficult to insert many rows.
Sequence number generators are normally the first choice. If you are trying to make your code independant of Oracle, consider using a function to return the next number to the application. One function for Oracle which does use sequences and a different one with the same name in another DB.
For an online application, you cant display or use the new PK elsewhere until the insert has been performed. Also, new records must be immediately committed to prevent possible duplicates.
|
|
|
|
] 
Blog
Search
Help
Members
Register
Login
Home
