Using sequences inside Composite Operations with Oracle EBS adapter
Consider a scenario where I want to insert data into two tables in a single transaction. The tables have a field that should be populated using the same sequence - one using SEQUENCE.NEXTVAL, and the other using SEQUENCE.CURRVAL, so that both tables get the same key value in these fields – a typical scenario when inserting into Interface Tables that have a dependency on each other.
The adapter performs all the operations in the Composite Operation in a single transaction and in order - however that does not mean that they are performed in a single session or connection to the Oracle server.
Sequences in Oracle are designed in a way such that their value does not reflect across sessions unless NEXTVAL is called on them. If you put 'SEQUENCE.NEXTVAL' in InlineValue for the first table and 'SEQUENCE.CURRVAL' in InlineValue for the second table in the Composite Operation, the result will not be what you’d normally expect. To elaborate, I defined a sequence named MYSEQUENCE, and opened two SQL-Plus sessions. I performed the following steps side by side as shown below:
Session 1 |
Session 2 |
SELECT MYSEQUENCE.CURRVAL FROM DUAL; ORA-08002: sequence MYSEQUENCE.CURRVAL is not yet defined in this session |
|
|
SELECT MYSEQUENCE.CURRVAL FROM DUAL; ORA-08002: sequence MYSEQUENCE.CURRVAL is not yet defined in this session |
SELECT MYSEQUENCE.NEXTVAL FROM DUAL; >>21 |
|
SELECT MYSEQUENCE.CURRVAL FROM DUAL; >>21 |
|
|
SELECT MYSEQUENCE.CURRVAL FROM DUAL; ORA-08002: sequence MYSEQUENCE.CURRVAL is not yet defined in this session |
|
SELECT MYSEQUENCE.NEXTVAL FROM DUAL; >>22 |
SELECT MYSEQUENCE.CURRVAL FROM DUAL; >>21 |
|
SELECT MYSEQUENCE.NEXTVAL FROM DUAL; >>23 |
|
As you see, the current value of the sequence does not get updated in all the sessions, and so if you need to use sequences in your inserts, straightforward logic does not work. We came up with two workarounds to make this work:
1. Fetch the value of the SEQUENCE.NEXTVAL beforehand (you can use the generic operation ExecuteScalar), and populate the final value in your message – works well if you’re using an orchestration.
2. Create two stored procedures on the Oracle backend – one for NEXTVAL, and another for CURRVAL that store the value of the sequence in a table (see attached sample script), so that the latest value is available across sessions.
In general, if a value that is session dependent needs to be used in various inserts in a composite operation, it should either be pre-fetched, or stored in a temporary table and accessed using functions called from InlineValue.