Re: How to simulate sequence numbers of Oracle

Sanjay Kumar (femski@netcom.com) wrote: : Hi Folks ! : I am trying to port a large ORACLE application to some of the other RDBMS : systems using ODBC as API. One of the things I need to deal with is : handling sequences. Oracle sequence number generator is an excelent : facility, unfortunately not supported by other systems (to my knowledge). If you're using Sybase System 10, read up on the IDENTITY column property. Pay close attention to the burning set factor. Note that the IDENTITY column property does not guarantee there won't be gaps in the numbers. For example, if you roll back a transaction, the IDENTITY value will not be reclaimed/recycled. Also, if there is a server crash, there will be a gap in IDENTITY values (the burning set factor controls the size of such gaps). If you are using a pre-System 10 server, then you have to implement it via code. I have done it by creating a table with 2 columns (col1 for the name of the table that needs the sequence number, and col2 for the next available number to be assigned). Insert triggers for all tables that require a sequence number execute a stored procedure that gets the next number and then increments it). It was not difficult to implement and it worked pretty well. This is one place where row level locks were important, so we created the sequence number table such that rows were padded to fill a page. The small waste of space was well worth what it saved our system in terms of locks on that critical table -- for us. Hope this helps Teresa Larson +----------------------------------------------------------------------+ | Teresa A. Larson ISUG Electronic Media Chair | | Loral AeroSys Voice: (301) 805-0494 | | 7375 Executive Place Fax: (301) 805-0444 | | Seabrook, MD 20706 Teresa_Larson@ccmail.aerosys.loral.com | +----------------------------------------------------------------------+ #include <std_disclaimer>