topic Re: Using SAP Number Range Objects vs. Using Oracle SEQUENCE in Application Development and Automation Discussions

Using SAP Number Range Objects vs. Using Oracle SEQUENCE

Former Member — Sun, 04 Feb 2024 01:32:53 GMT

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.

Re: Using SAP Number Range Objects vs. Using Oracle SEQUENCE

RichHeilman — Thu, 03 Nov 2005 20:13:11 GMT

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

Re: Using SAP Number Range Objects vs. Using Oracle SEQUENCE

FredericGirod — Thu, 03 Nov 2005 20:30:24 GMT

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

Re: Using SAP Number Range Objects vs. Using Oracle SEQUENCE

RichHeilman — Thu, 03 Nov 2005 20:35:52 GMT

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

Regards,

RIch Heilman

Re: Using SAP Number Range Objects vs. Using Oracle SEQUENCE

Former Member — Thu, 03 Nov 2005 20:43:00 GMT

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

Re: Using SAP Number Range Objects vs. Using Oracle SEQUENCE

thomas_jung — Thu, 03 Nov 2005 20:51:14 GMT

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.

Re: Using SAP Number Range Objects vs. Using Oracle SEQUENCE

Former Member — Thu, 03 Nov 2005 21:09:13 GMT

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