Using SAP Number Range Objects vs. Using Oracle SEQUENCE

[Former Member]

Hi -

I'm just wondering if anyone out there has investigated using SAP number range objects vs. the Oracle SEQUENCE (which needs to be executed using native SQL) - and could recommend which way would be better.

I'm definitely leaning toward the SAP solution b/c of the flexibility of the number range.

I need to generate a sequential number which I'm using as a primary index to a table. I've identified the following ways to do this:

- SAP number range object ... where you setup a number range object using the OYSN transaction - and then in your code, you use the following functions:

number_range_enqueue - to lock number range object

number_get_next - to get the next sequential number

number_range_dequeue - to unlock number range object

- Or you can use the native Oracle SEQUENCE function - and within your code call native sql to get the next value. Example is below

EXEC SQL .

SELECT MySequence.NEXTVAL

INTO :W_NEXT_SEQUENCE_ID FROM DUAL

ENDEXEC.

[RichHeilman]

I would definitly recommend using the Number ranges in SAP. Looks like OYSN is for IDOCs, you can use SNRO for other number ranges. Most of the ABAPers here, will suggest using the number ranges in almost every case.

Regards,

Rich Heilman

[FredericGirod]

I'm join Rich.

SNRO is more usefull than Oracle, and when you use SAP you must forget the database. Just for example, if a day you have to change your database (oracle to -> SAPdb .. or anything else).

Rgd

Frédéric

[RichHeilman]

Good point Frederic. Couldn't have said it better myself.

Regards,

RIch Heilman

[Former Member]

I will definetely go with others here in recommending number ranges. Number ranges will abstract the database layer for you so that you don't have to know which underlying database you are dealing with. Next, in a multiple database instance scenario, how do ensure that your next sequence number is same irrespective of which database you logged onto? I don't know if the native command gives you the flexibility of allowing you to limit the numbers to a maximum and also provide you with a warning message when the number range is coming closer to the max value.

Srinivas

[thomas_jung]

Don't forget that SAP's number ranges also have an available buffering mechanism. You can configure them to retrieve several sets of numbers in the sequence and buffer them at the application server layer. This is great if you are generating large volumes of records and don't need to account for every single number in the sequence.

I don't believe that you need to call the enqueue/dequeue functions when just getting the next number. According to the documentation on the functions, these locks are only needed when you are going to change a Number Range Object/Group/Interval.

[Former Member]

Thanks for the quick feedback ... number ranges it is ...