Kbase P15726: What are the differences between Progress sequence generator and Oracle sequence generator?
| Autor |
Progress Software Corporation - Progress |
| Acesso |
Público |
| Publicação |
9/8/2008 |
|
Status: Verified
GOAL:
What are the differences between Progress sequence generator and Oracle sequence generator?
GOAL:
What could cause a "hole" in Oracle sequence values?
FACT(s) (Environment):
Oracle
Oracle DataServer
All Supported Operating Systems
FIX:
There are 3 major differences:
1. Progress can set current value of a sequence, Oracle can not. This limitation can be viewed from dbrestrictions 4GL function.
Possible return values for Oracle
"LAST,PREV,READ-ONLY,RECID,SETUSERID,SET-CURRENT-VALUE"
For more information on dbrestrictions, please reference Progress Language Reference.
2. Oracle tends to cache certain number of sequence numbers when multiple users are accessing the sequence generator. A typical senario works like following:
User A accesses sequence and gets current value of 2, user B accesses the sequence and gets current value of 9, next when user A asks for next value, it will get 3 instead of 10. If user A rolls back his transaction, the sequence numbers between 3-8 will be lost (creating a hole).
The default cache is 20, but this can be altered to zero using NOCACHE as required
alter sequence myseq nocache;
To check the next sequence value
select myseq.nextval from system.dual;
3. Reference solution P14187.