Process millions of records from Oracle Database | Oracle Integration
As we know Oracle Integration has service limits in terms of the size of data you can fetch from the Oracle database. Is this mean you can not pull millions of records using Oracle Integration? This is not correct, you can process millions of records in Oracle Integration that resides in the Oracle database.
The solution to process millions of records
To solve the issue of the service limit which is 10MB, you can get the data in a small chunk in a while loop and push the records into the target application. Run the while loop until all records are processed from the database.
To fetch the data into chunks from DB, you have to use the following two parameters:
- offset: How many previous records to skip
- limit: How many records to fetch in a single select query.
Following is the sample query to fetch records in chunks
select * from employee order by id OFFSET #pOffset ROWS FETCH NEXT #pLimit ROWS ONLY
#pOffset and #pLimit are the parameters that you can pass in the mapper.
To know how to process millions of records from DB, I have created a nice video end-to-end. Look at the following video:
If you like this article, please like, comment, and share. Request you to subscribe to my YouTube channel for more such videos and don’t forget to subscribe to our channel to get regular updates.
Further readings:
Place the file in ATP from BIP using OIC
Place the file in Object Storage from BIP using OIC
How to write files using an OIC FTP adapter
Read files from Object Storage in Oracle Integration
