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 -  Read the complete article here.

PaaS Partner Community

For regular information on Oracle PaaS become a member in the PaaS (Integration & Process) Partner Community please register here.

Blog Twitter LinkedIn Facebook Wiki

Technorati Tags: SOA Community,Oracle SOA,Oracle BPM,OPN,Jürgen Kress