Welcome to All Things Data Integration: Announcements, Insights, Best Practices, Tips & Tricks, and Trend Related...

  • February 2, 2018

Oracle Data Integrator (ODI) to Extract Data from Fusion Application (Oracle ERP Cloud)

Sandrine Riley
Product Management - Data Integration Solutions at Oracle

Guest Author:  Srinidhi Koushik, Senior Sales Consultant at Oracle

Oracle Data Integrator (ODI) to extract data from Fusion Application (Oracle ERP Cloud)

In continuation to the previous blog wherein we discussed about loading data onto ERP Cloud, we will now discuss how to extract data from ERP Cloud using Oracle Data Integrator (ODI). ODI is available on-premises as well as in the Oracle Cloud with Data Integration Platform Cloud and ODI Cloud Service.

Overview of the integration process

In this blog, we will discuss the steps involved in downloading data from ERP Cloud. The steps involved are listed below

  1. The construct of data extraction is executed via the Web service call which in turn invokes the BI publisher report associated with the web service call
  2. The response of the Web Service call is a BASE64 construct string against “ReportBytes” tag


sed '/<ns2:reportBytes>/!d;s//\n/;s/[^\n]*\n//;:a;$!{/<\/ns2:reportBytes>/!N;//!ba};y/\n/ /;s/<\/ns2:reportBytes>/\n/;P;D' /home/oracle/Elogs/Report2_Resp.xml | tr ' ' '\n'

c. The Java program is used to decode the BASE64 onto required format of file

Java Construct of Program:

import java.io.ByteArrayOutputStream;

import java.io.File;

import java.io.FileInputStream;

import java.io.FileOutputStream;

import java.io.FileReader;

import java.io.IOException;

import java.io.InputStream;

import java.io.BufferedReader;

import java.io.FileReader;


import org.apache.commons.codec.binary.Base64;


public class DecryptBaseFile {

    public DecryptBaseFile() {




    public static void main(String args[]) {

        BufferedReader br = null;

        FileReader fr = null;


        try {

            fr = new FileReader("/home/oracle/Elogs/ReportBytes.txt");

            br = new BufferedReader(fr);


            StringBuffer fileData = new StringBuffer();

            String sCurrentLine;


            while ((sCurrentLine = br.readLine()) != null) {



            System.out.println("Reading data done from file /home/oracle/Elogs/ReportBytes.txt");


            // Run the api to perform the decoding

            byte[] rbytes = Base64.decodeBase64(fileData.toString().getBytes());


            FileOutputStream os = new FileOutputStream("/home/oracle/TgtFiles/Customer.csv");



            System.out.println("File Customer.csv created successfully at /home/oracle/TgtFiles");

        } catch (Exception e) {


            System.out.println("Error:Kindly contact admin");





d. The created file is then copied onto source directory

e. The file is then loaded onto necessary tables

The ‘OdiInvokeWebService’ construct provided below

Please note:

  1. OWSM rule that needs to be selected is oracle/wss_username_token_over_ssl_client_policy

Requirement for this to work is to use ODI Enterprise configured with an Agent deployed in WebLogic Server

The Topology associated with the report generation can be found below

Inputs Sought by Web service:


The features available in ODI make it relatively simple to generate a file, compress / zip the file and call specific Java / Linux scripts / functions and invoking necessary web services on ERP cloud to download the necessary data onto any specific format like csv, html, txt, excel, etc.

Be the first to comment

Comments ( 0 )
Please enter your name.Please provide a valid email address.Please enter a comment.CAPTCHA challenge response provided was incorrect. Please try again.