DB Adapter Polling - Using MAXTRANSACTIONSIZE and Ordering Clause does not Guarantee the Order (Doc ID 1911488.1)

Last updated on OCTOBER 18, 2016

Applies to:

Oracle Service Bus - Version 11.1.1.7.0 and later
Information in this document applies to any platform.

Symptoms


Using MaxTransactionSize = and ordering clause on a specific column,
the generated SELECT statement doesn't guarantee the ordered polling of the events based on the column specified.

In fact, the generated SELECT is the following:

  AND (ROWNUM
Using ROWNUM and ORDER BY at the same level, as documented here
Oracle® Database SQL Reference
10g Release 1 (10.1)
Part Number B10759-01
ROWNUM
http://docs.oracle.com/cd/B12037_01/server.101/b10759/pseudocolumns008.htm

the order of the events is not keept.
It should be used a nested SELECT statement with ORDER BY, used by an external SELECT statement using ROWNUM:

SELECT ... FROM
( SELECT ... FROM ...
  WHERE ...
  ORDER BY ....
)
WHERE ROWNUM < xxxxx;


Cause

Sign In with your My Oracle Support account

Don't have a My Oracle Support account? Click to get started

My Oracle Support provides customers with access to over a
Million Knowledge Articles and hundreds of Community platforms