OIC/Integration/15 → Fetch records of size more than 10 MB from DB and write to a file using DB pagination

10 min readSep 9, 2023

This is a post on Oracle Integration Cloud highlighting limitation of fetching records from DB when the file size is more than 10 MB post transformation. Here we will also, discuss how to overcome the limitation while fetching records from DB for size more that 10 MB using DB pagination.

(1) Flow: [a] Trigger → [b] Fetch row size from Lookup→ [c] Loop → [c.1] Map to Fetch DB Batch records → [c.2] Fetch DB Batch records → [c.3] Switch → [c.3.1] IF total DB record count is greater than 0 , [c.3.2] ELSE-IF Pending record count is greater than row size, [c.3.3] ELSE-IF Pending record count is less than row size, [c.3.4] Else condition → [c.4] Switch 2 → [c.5] Map to Write FTP File → [c.6] Write FTP File → [d] Map to Reply → [e] Reply.

(2) Flow Description: Here we are creating an App Driven Orchestration flow exposing via REST which can be invoked providing DB table name. Based on the table name the flow will search in a static look-up DVM (Domain Value Map) against the table name and fetch the corresponding Row Size (maximum number of records the flow can process in a single run). After fetching those many records the flow will write the file in a FTP location. Within each loop, the flow will append the existing flow. As the flow is written based on a legacy (before Oracle 12c) version of Database here we will be using RowNum in SQL query, whereas post Oracle DB 12c the OFFSET and FETCH NEXT clauses are used for the pagination of Oracle DB.

--older version:

SELECT org_id,org_name,country,estd_year,industry,headcount FROM 
(SELECT org_id,org_name,country,estd_year,industry,headcount,
ROW_NUMBER() over (ORDER BY org_id) RowNumber
 FROM org)
WHERE RowNumber BETWEEN 11 AND 21;

-- in Oracle 12c
SELECT org_id,org_name,country,estd_year,industry,headcount 
FROM org ORDER BY org_Id
OFFSET 10 ROWS FETCH NEXT 5 ROWS ONLY;

(3) Steps:

** Pre-Requisite:

Need to create a “ORG” table.

CREATE TABLE "<schema>"."ORG" 
( "ORG_ID" NUMBER NOT NULL ENABLE, 
  "ORG_NAME" VARCHAR2(200 BYTE) NOT NULL ENABLE, 
  "COUNTRY" VARCHAR2(100 BYTE), 
  "ESTD_YEAR" VARCHAR2(20 BYTE), 
  "INDUSTRY" VARCHAR2(80 BYTE), 
  "HEADCOUNT" NUMBER
);

Insert below records in csv file in ORG table:

Download the file “organizations-500000.csv” from below link. Only select columns based on table structure and remove others from the csv file. Can modify data as per the need.

Download Sample CSV Files for free - Datablist

You can download sample CSV files here for testing purposes. The datasets can be used in any software application…

www.datablist.com

[a] Trigger:

Use the following XSD , “DBFetchRecBatchSchema.xsd” :

<?xml version="1.0" encoding="windows-1252" ?>
<xsd:schema xmlns:xsd="http://www.w3.org/2001/XMLSchema" xmlns="http://www.rest.db.fetch.rec.batch.com"
            targetNamespace="http://www.rest.db.fetch.rec.batch.com" elementFormDefault="qualified">
  <xsd:element name="dbFetchReq">
    <xsd:complexType>
      <xsd:sequence>
        <xsd:element name="dbTableName" type="xsd:string" minOccurs="0"/>
      </xsd:sequence>
    </xsd:complexType>
  </xsd:element>
  <xsd:element name="dbFetchResp">
    <xsd:complexType>
      <xsd:sequence>
        <xsd:element name="result" type="xsd:string" minOccurs="0"/>
      </xsd:sequence>
    </xsd:complexType>
  </xsd:element>
</xsd:schema>

[c.2] Fetch DB Batch records:

Put the query & validate:

SELECT ORG_ID, ORG_NAME, COUNTRY, ESTD_YEAR, INDUSTRY, HEADCOUNT FROM <schema>.ORG

[c.1] Map to Fetch DB Batch records:

<dbFetchReq xmlns="http://www.rest.db.fetch.rec.batch.com">
      <dbTableName>dbTableName1</dbTableName>
</dbFetchReq>

To avoid this issue, we will add one package and implement a procedure.

--------------------------------------------------------
--  DDL for Package PKG_ORG_DTLS
--------------------------------------------------------

CREATE OR REPLACE PACKAGE "<schema>"."PKG_ORG_DTLS" AS 

PROCEDURE proc_fetch_org (p_start_index      IN NUMBER,
                            p_end_index        IN NUMBER,
                            org_rec_cur        OUT SYS_REFCURSOR,
                            total_rec_cnt      OUT NUMBER,
                            status             OUT VARCHAR2,
                            status_desc        OUT VARCHAR2);

END PKG_ORG_DTLS;

/

--------------------------------------------------------
--  DDL for Package Body PKG_ORG_DTLS
--------------------------------------------------------

CREATE OR REPLACE PACKAGE BODY "<schema>"."PKG_ORG_DTLS" AS

PROCEDURE proc_fetch_org (p_start_index      IN NUMBER,
                            p_end_index        IN NUMBER,
                            org_rec_cur        OUT SYS_REFCURSOR,
                            total_rec_cnt      OUT NUMBER,
                            status             OUT VARCHAR2,
                            status_desc        OUT VARCHAR2)
IS
  BEGIN
      IF p_start_index = 0 THEN
          SELECT COUNT(1) INTO total_rec_cnt FROM RRJ.ORG;
      ELSE
          total_rec_cnt := -1;
      END IF;
        
      OPEN org_rec_cur FOR
      SELECT ORG_ID,ORG_NAME,COUNTRY,ESTD_YEAR,INDUSTRY,HEADCOUNT 
      FROM (SELECT ORG_ID,ORG_NAME,COUNTRY,ESTD_YEAR,INDUSTRY,HEADCOUNT,ROW_NUMBER() OVER (ORDER BY ORG_ID) RNO FROM <schema>.ORG)
      WHERE RNO BETWEEN p_start_index AND p_end_index;

      status := '0';
      status_desc := 'SUCCESS';
        
  EXCEPTION
      WHEN OTHERS THEN
      status := '-1';
      status_desc := SQLERRM;
        
END proc_fetch_org;

END PKG_ORG_DTLS;

/

[c.2] Fetch DB Batch records (updating):

In the OIC Console navigation pane, click Home > Integrations > Lookups.

Create Lookup:

[b] Fetch row size from Lookup:

Add variable → “VarRowSize”. Expand Functions > Integration Cloud.

[c] Loop:

[c.3] Switch:

[c.3.1] IF total DB record count is greater than 0:

Completed IF branch : used it for initial assignment of total DB record count to a variable.

During 1st DB procedure call there won’t be any rows being returned, just total record count ; as we are sending start and end index both as 0. This we are doing to just to re-use the procedure for getting total record count from DB.

[c.3.2] ELSE-IF Pending record count is greater than row size:

Completed: ELSE-IF-1 : used it for scenario when Pending record count is greater than Row Size. Means, more than 1 loop to execute.

[c.3.3] ELSE-IF Pending record count is less than row size:

Completed : ELSE-IF-2 : used it for scenario when Pending record count is lesser than Row Size. Means, just 1 last loop to execute.

[c.3.4] Else condition:

This will be executed as part of else. Do Nothing and continue. Can add exception handling here.

[c.1] Map to Fetch DB Batch records:

[c.6] Write FTP File:

Use the following XSD, “Org_Out_Schema.xsd”:

<?xml version = '1.0' encoding = 'UTF-8'?>
<xs:schema targetNamespace="http://xmlns.oracle.com/cloud/adapter/ftp/OrgOutRecords/types" xmlns="http://xmlns.oracle.com/cloud/adapter/ftp/OrgOutRecords/types" elementFormDefault="qualified" attributeFormDefault="qualified" xmlns:xs="http://www.w3.org/2001/XMLSchema">
   <xs:element name="OrgRecordsOutput" type="OrgRecordsOutputType"/>
   <xs:complexType name="OrgRecordsOutputType">
      <xs:sequence/>
   </xs:complexType>
   <xs:element name="OrgRecordsOutputCollection" type="OrgRecordsOutputCollection"/>
   <xs:complexType name="OrgRecordsOutputCollection">
      <xs:sequence>
         <xs:element name="OrgRecordsOutput" type="OrgRecordsOutput" minOccurs="0" maxOccurs="unbounded"/>
      </xs:sequence>
   </xs:complexType>
   <xs:complexType name="OrgRecordsOutput">
      <xs:sequence>
         <xs:element name="ORG_ID" type="xs:decimal" nillable="true"/>
         <xs:element name="ORG_NAME" type="xs:string" nillable="true"/>
         <xs:element name="COUNTRY" type="xs:string" nillable="true"/>
         <xs:element name="ESTD_YEAR" type="xs:string" nillable="true"/>
         <xs:element name="INDUSTRY" type="xs:string" nillable="true"/>
         <xs:element name="HEADCOUNT" type="xs:decimal" nillable="true"/>
      </xs:sequence>
   </xs:complexType>
</xs:schema>

[c.5] Map to Write FTP File:

Put following mapping for FTP file name : “outFile.xml”

[c.4] Switch 2:

Additionally using another Switch to check the File write flag. For 1st iteration on DB procedure, where we are just fetching the Row Count, we will skip file write, else a blank unwanted row will get inserted in target file.

<dbFetchReq xmlns="http://www.rest.db.fetch.rec.batch.com">
      <dbTableName>ORG</dbTableName>
</dbFetchReq>

Going to test with above payload. Based on “dbTableName”, from DVM Row Size (50,000 records in every iteration) is fetched and used in the flow.

Not all records got written into the file. After 7th iteration the transaction got closed as the Synchronous transaction closes Transaction after 5 minutes, total 7x50,000=3,50,000 records got processed.

Now we will change the exposed REST service from Request-Response pattern to One-way pattern like below.

Increasing Row Size to 70,000 records causes error , as increasing record count increases payload size more than 10 MB and transaction gets failed.

Re-modelling of the flow: