Thursday, April 21, 2022

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:

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)
OPEN p_Orders for
SELECT "orderNumber","Customer" 
FROM orders
ORDER BY "orderNumber"
OFFSET in_offset ROWS

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


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

No comments:

Post a Comment

Featured Post

11g to 12c OSB projects migration points

1. Export 11g OSB code and import in 12c Jdeveloper. Steps to import OSB project in Jdeveloper:   File⇾Import⇾Service Bus Resources⇾ Se...