#891 OIC -> DB adapter - overcoming 10 MB limit

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[1], " / ",$currentOrderRow/nsmpr3:Row/nsmpr3:Column[2], " / ",$currentOrderRow/nsmpr3:Row/nsmpr3:Column[3]) 

 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 -