Database Adapter Inserts Data in Different Order then that Defined in the BPEL Variable

(Doc ID 1421778.1)

Last updated on OCTOBER 10, 2016

Applies to:

Oracle SOA Suite - Version 11.1.1.5.0 and later
Information in this document applies to any platform.

Symptoms

A database adapter is used in order to insert multiple rows into the database.
The database adapter does not insert the rows in the same order as they were specified in the variable from the bpel instance.

- Database

//drop table Test;
//drop sequence Sequence_Test;
//drop trigger Trigger_Test;

CREATE TABLE Test (
id NUMBER(3),
str VARCHAR2(20),
nr  NUMBER(3)
);

CREATE SEQUENCE Sequence_Test MINVALUE 1 MAXVALUE 1000000000000000000000000000 INCREMENT BY 1 START WITH 761 CACHE 20 NOORDER NOCYCLE;

CREATE OR REPLACE TRIGGER Trigger_Test BEFORE
INSERT ON Test FOR EACH ROW BEGIN
SELECT Sequence_Test.NEXTVAL INTO :NEW.nr FROM dual;
END;

ALTER TRIGGER Trigger_Test ENABLE;


- BPEL Variable

<variable>
   <id>1</id><str>str1</str>
   <id>2</id><str>str2</str>
   <id>3</id><str>str3</str>
   ...
   <id>50</id><str>str50</str>
</variable>


After the insert if you make a select the index column will not have the same order with id:

select * from Test order by nr;
  ID            STR           NR
----   ------------   ----------
   4           str4            1
   7           str7            2
  11          str11            3
...

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