OIC - DB Procedure call | DB adapter 10 MB limit overcoming solution

Use Case: If we run a db select query and it has humungous data (more than 10MB) then it will fail as it has reached the limit. We can avoid such error using  SQL with offset. In this use case, we will use a Stored procedure.

To know about Offset & Fetch:

https://soalicious.blogspot.com/2022/04/db-offset-fetch-clause.html

Highlevel steps:

1. Create a db table orders.
2. Create a offset select db query
3. Wrap the sql query in a stored procedure.
4. In the integration, 
1. 1st we take the total row count using db adaptet call
2. Determine number of iteration - ceiling(totalRows / batch size)
3. For each iteration,
1. We will select the rows by offset
2. For each row, we will log the row details
3. Increment the counter and offset.

Steps in detail:

Create a table orders:

Create table orders(

"orderNumber" VARCHAR2(20 BYTE),

"Customer" VARCHAR2(200 BYTE)

)

Select offset db query:

Select "orderNumber","Customer" from orders order by "orderNumber"

Offset 2 rows 

Fetch Next 3 rows only

Sql query wrapped up in stored procedure:

CREATE OR REPLACE PROCEDURE OrdersByOffset(in_fetchSize NUMBER, in_offset NUMBER, p_Orders out SYS_REFCURSOR)

IS

BEGIN

OPEN p_Orders for

SELECT "orderNumber","Customer" 

FROM orders

ORDER BY "orderNumber"

OFFSET in_offset ROWS

FETCH NEXT in_fetchSize ROWS ONLY;

END;

/

Create a scheduled orchestration and count total rows from the dp adapter using the following sql:

Select count(1) from orders

Total rows fetch db adaptet config:

Create variables for :

totalRows: number of rows fetched from db adapter.

batchSize: number(4.0)

totalIteration: ceiling(orajs29:divide($totalRows,$batchSize))

Next step is to invoke stored procedure by offset to get the batch of rows.

Assign Counter and Offset:

Counter: number(1)

fetchSize: $batchSize

offSet: number(0)

In the while,

$Counter <=$totalIteration

Invoke stored procedure.

Map the fetchSize and Offset.

Now for each fetched row, will log them and increment the counter and offset.

For each row,

Log the row details:

concat("Order Details - OrderNr / Customer :",$currentRow/nsmpr3:Row/nsmpr3:Column[1]," / ",$currentRow/nsmpr3:Row/nsmpr3:Column[2])

Counter: $Counter + 1

offSet : $offSet + $fetchSize

Testing:

In my case, i have totalRows = 9 in the db orders table and batchSize= 4, so total iterations=9 / 4 = 3.