This is an issue with many customers - I execute a SELECT against my on-premise Oracle DB from OIC and a humungous amount of data is returned. This exceeds the 10 MB limit and causes an error.
Here is a simple example of avoiding such.
Here is my test table -
I can use SQL with OFFSET -
SELECT orderNr, customer, value FROM orders ORDER BY ordernr OFFSET 2 ROWS FETCH NEXT 3 ROWS ONLY;
I wrap this SQL in a stored procedure -
Now to my integration, which will invoke the Stored Procedure. This is a scheduled orchestration -
First step is to do a SELECT COUNT(1) FROM ORDERS - this returns 16 in my case.
Now for my simple example I will process these in batches of 5. You can extrapolate from this e.g. 200 rows is well within my 10MB limit.
I then divide my count by the batch size - using the ceiling function.
ceiling(16 / 5) = 4 iterations.
Next step is to leverage the DB adapter to invoke the PL/SQL stored procedure -
The DB invoke Mapping is as follows -
The order data is returned in the following format, because we are using a refCursor -
So I need to remember the sequence here - column 1 is ORDERNR, column 2 is CUSTOMER and column 3 is VALUE.
As you can see above, the only processing I have is logging the order details - here is the configuration of the LOG Action -
concat("Order Details - OrderNr / Customer / Value : ",$currentOrderRow/nsmpr3:Row/nsmpr3:Column, " / ",$currentOrderRow/nsmpr3:Row/nsmpr3:Column, " / ",$currentOrderRow/nsmpr3:Row/nsmpr3:Column)
Note also, the last action is to update the variable I am using for the offset value.
I run the integration and check out the activity stream -
4 iterations are executed - Iteration 1 has the following orders -
Iteration 2 has the following orders -
Iteration 3 orders -
Iteration 4 orders -