Sometimes we need to export some data from Oracle ERP / SCM Cloud.

However, Oracle didn’t provide us direct connections to the backend database.

There are 2 ways we can get the data back from ERP Cloud, one is through the web services (soap/restful) defined for different data objects, another is through the BI Publisher calls to export a data only report.

This blog shows how to use Groovy / Java to export a BI Publisher Report from Oracle ERP Cloud – Normally we will create some standalone reports within BI Publisher, and make its output to be csv only or xml only.

Following is the code that shows how to extract it.

The code uses standard apache http client package which can be found here: Apache HttpClient

import org.apache.http.HttpResponse;
import org.apache.http.client.ClientProtocolException;
import org.apache.http.client.HttpClient;
import org.apache.http.client.methods.HttpPost;
import org.apache.http.entity.StringEntity;
import org.apache.http.impl.client.HttpClientBuilder;
import com.sun.org.apache.xml.internal.security.utils.Base64;
import java.text.MessageFormat;


String url = "https://YOUR_ERP_CLOUD_ENVIRONMENT/xmlpserver/services/ExternalReportWSSService";
String authStr = "USERNAME:PASSWORD";
HttpClient client = HttpClientBuilder.create().build();
HttpPost post = new HttpPost(url);

// set credential
byte[] authBytes = authStr.getBytes("UTF-8");
String auth = Base64.encode(authBytes);
post.setHeader("Authorization", "Basic " + auth);
post.setHeader("Content-Type", "application/soap+xml;charset=UTF-8");

String payload =
	'''
	<soap:Envelope xmlns:pub="http://xmlns.oracle.com/oxp/service/PublicReportService" xmlns:soap="http://www.w3.org/2003/05/soap-envelope">
		<soap:Body>
			<pub:runReport>
				<pub:reportRequest>
					<pub:attributeFormat>xml</pub:attributeFormat>
					<pub:attributeLocale/>
					<pub:attributeTemplate/>
					<pub:reportAbsolutePath>/Custom/REPORT_PATH/REPORT_NAME.xdo</pub:reportAbsolutePath>
					<pub:sizeOfDataChunkDownload>-1</pub:sizeOfDataChunkDownload>
					<pub:parameterNameValues>
						<pub:item>
							<pub:label>PARAMETER1</pub:label>
							<pub:name>PARAMETER1</pub:name>
							<pub:values>
								<pub:item>{0}</pub:item>
							</pub:values>
						</pub:item>
					</pub:parameterNameValues>
				</pub:reportRequest>
				<pub:appParams/>
			</pub:runReport>
		</soap:Body>
	</soap:Envelope>
	'''

payload = MessageFormat.format(payload, "PARAMETER_VALUE");

post.setEntity(new StringEntity(payload));

// execute the request and get the response
HttpResponse response = client.execute(post);

// get the result, you can use apache IOUtils to convert the contents InputStream to string 
BufferedReader rd = new BufferedReader(new InputStreamReader(response.getEntity().getContent()));
StringBuffer result = new StringBuffer();
String line = "";
while ((line = rd.readLine()) != null) {
	result.append(line);
}
print( result.toString());