Loading Data into Oracle BI Cloud Service using BI Publisher Reports and SOAP Web Services


This post details a method of loading data that has been extracted from Oracle Business Intelligence Publisher (BIP) into the Oracle Business Intelligence Cloud Service (BICS). The BIP instance may either be Cloud-Based or On-Premise.

It builds upon the A-Team post Using Oracle BI Publisher to Extract Data from Oracle Sales and ERP Clouds. This post uses SOAP web services to extract data from an XML-formatted BIP report.

The method uses the PL/SQL language to wrap the SOAP extract, XML parsing commands, and database table operations. It produces a BICS staging table which can then be transformed into star-schema object(s) for use in modeling.  The transformation processes and modeling are not discussed in this post.

Additional detailed information, including the complete text of the procedure described, is included in the References section at the end of the post.

Rationale for using PL/SQL

PL/SQL is the only procedural tool that runs on the BICS / Database Schema Service platform. Other wrapping methods e.g. Java, ETL tools, etc. require a platform outside of BICS to run on.

PL/SQL can utilize native SQL commands to operate on the BICS tables. Other methods require the use of the BICS REST API.

Note: PL/SQL is a very good at showcasing functionality. However, it tends to become prohibitively resource intensive when deploying in an enterprise production environment.

For the best enterprise deployment, an ETL tool such as Oracle Data Integrator (ODI) should be used to meet these requirements and more:

* Security

* Logging and Error Handling

* Parallel Processing – Performance

* Scheduling

* Code re-usability and Maintenance

The steps below depict how to load a BICS table.

About the BIP Report

The report used in this post is named BIP_DEMO_REPORT and is stored in a folder named Shared Folders/custom as shown below:

BIP Report Location

The report is based on a simple analysis with three columns and output as shown below:

BIP Demo Analysis

Note: The method used here requires all column values in the BIP report to be NOT NULL for two reasons:

1. The XPATH parsing command signals either the end of a row or the end of the data when a null result is returned.

2. All columns being NOT NULL ensures that the result set is dense and not sparse. A dense result set ensures that each column is represented in each row. Additional information regarding dense and sparse result sets may be found in the Oracle document Database PL/SQL Language Reference.

One way to ensure a column is not null is to use the IFNull function in the analysis column definition as shown below:


Call the BIP Report

The SOAP API request used here is similar to the one detailed in Using Oracle BI Publisher to Extract Data from Oracle Sales and ERP Clouds.

The SOAP API request should be constructed and tested using a SOAP API testing tool e.g. SoapUI.

This step uses the APEX_WEB_SERVICE package to issue the SOAP API request and store the XML result in a XMLTYPE variable. The key inputs to the package call are:

* The URL for the Report Request Service

* The SOAP envelope the Report Request Service expects.

* Optional Headers to be sent with the request

* An optional proxy override

Note: Two other BI Publisher reports services exist in addition to the one shown below. The PublicReportService_v11 should be used for BI Publisher 10g environments and the ExternalReportWSSService should be used when stringent security is required. An example URL is below:


An example Report Request envelope is below:

<soapenv:Envelope xmlns:soapenv=”http://schemas.xmlsoap.org/soap/envelope/” xmlns:v2=”http://xmlns.oracle.com/oxp/service/v2″>

An example of setting a SOAP request header is below:

apex_web_service.g_request_headers(1).name :=SOAPAction‘; apex_web_service.g_request_headers(1).value := ”;

An example proxy override is below:


 Putting this together, example APEX statements are below:

apex_web_service.g_request_headers(1).name := ‘SOAPAction’;                  apex_web_service.g_request_headers(1).value := ”;                  f_xml := apex_web_service.make_request(p_url => p_report_url, p_envelope => l_envelope, p_proxy_override => l_proxy_override );

Note: The SOAP header used in the example above was necessary for the call to the BI Publisher 11g implementation used in a demo Sales Cloud instance. If it were not present, the error LPX-00216: invalid character 31 (0x1F) would appear. This message indicates that the response received from the server was encoded in a gzip format which is not a valid xmltype data type.

Parse the BIP Report Result Envelope

This step parses the XML returned by the SOAP call for the data stored in the tag named reportBytes that is encoded in Base64 format.

The XPATH expression used below should be constructed and tested using an XPATH testing tool e.g. freeformatter.com

This step uses the APEX_WEB_SERVICE package to issue parsing command and store the result in a CLOB variable. The key inputs to the package call are:

* The XML returned from BIP SOAP call above

* The XML Path Language (XPATH) expression to find the reportBytes data

An example of the Report Response envelope returned is below:

<soapenv:Envelope xmlns:soapenv=”http://schemas.xmlsoap.org/soap/envelope/” xmlns:xsd=”http://www.w3.org/2001/XMLSchema” xmlns:xsi=”http://www.w3.org/2001/XMLSchema-instance”><soapenv:Body><runReportResponse xmlns=”http://xmlns.oracle.com/oxp/service/v11/PublicReportService”><runReportReturn>        <reportBytes>PD94bWwgdmVyc2lvbj0iMS4wIiBlbmNvZGluZz0iVVRGLTgiPz4KPCEtLUdlbmVyYXRlZCBieSBPcmFjbGUgQkkgUHVibGlzaGVyIDEyLjIuMS4xLjAgLURhdGFlbmdpbmUsIGRhdGFtb2RlbDpfY3VzdG9tX0JJUF9ERU1PX01PREVMX3hkbSAtLT4KPERBVEFfRFM+PFNBVy5QQVJBTS5BTkFMWVNJUz48L1NBVy5QQVJBTS5BTkFMWVNJUz4KPEdfMT4KPENPTFVNTjA+QWNjZXNzb3JpZXM8L0NPTFVNTjA+PENPTFVNTjE+NTE2MTY5Ny44NzwvQ09MVU1OMT48Q09MVU1OMj40ODM3MTU8L0NPTFVNTjI+CjwvR18xPgo8R18xPgo8Q09MVU1OMD5BdWRpbzwvQ09MVU1OMD48Q09MVU1OMT43MjM3MzYyLjM8L0NPTFVNTjE+PENPTFVNTjI+NjI3OTEwPC9DT0xVTU4yPgo8L0dfMT4KPEdfMT4KPENPTFVNTjA+Q2FtZXJhPC9DT0xVTU4wPjxDT0xVTU4xPjY2MTQxMDQuNTU8L0NPTFVNTjE+PENPTFVNTjI+NDAzNzQ0PC9DT0xVTU4yPgo8L0dfMT4KPEdfMT4KPENPTFVNTjA+Q2VsbCBQaG9uZXM8L0NPTFVNTjA+PENPTFVNTjE+NjMyNzgxOS40NzwvQ09MVU1OMT48Q09MVU1OMj40Nzg5NzU8L0NPTFVNTjI+CjwvR18xPgo8R18xPgo8Q09MVU1OMD5GaXhlZDwvQ09MVU1OMD48Q09MVU1OMT44ODA3NzUzLjI8L0NPTFVNTjE+PENPTFVNTjI+NjU1MDY1PC9DT0xVTU4yPgo8L0dfMT4KPEdfMT4KPENPTFVNTjA+SW5zdGFsbDwvQ09MVU1OMD48Q09MVU1OMT40MjA4ODQxLjM5PC9DT0xVTU4xPjxDT0xVTU4yPjY2MTQ2OTwvQ09MVU1OMj4KPC9HXzE+CjxHXzE+CjxDT0xVTU4wPkxDRDwvQ09MVU1OMD48Q09MVU1OMT43MDAxMjUzLjI1PC9DT0xVTU4xPjxDT0xVTU4yPjI2OTMwNTwvQ09MVU1OMj4KPC9HXzE+CjxHXzE+CjxDT0xVTU4wPk1haW50ZW5hbmNlPC9DT0xVTU4wPjxDT0xVTU4xPjQxMjAwOTYuNDk8L0NPTFVNTjE+PENPTFVNTjI+NTI3Nzk1PC9DT0xVTU4yPgo8L0dfMT4KPEdfMT4KPENPTFVNTjA+UGxhc21hPC9DT0xVTU4wPjxDT0xVTU4xPjY2Njk4MDguODc8L0NPTFVNTjE+PENPTFVNTjI+Mjc4ODU4PC9DT0xVTU4yPgo8L0dfMT4KPEdfMT4KPENPTFVNTjA+UG9ydGFibGU8L0NPTFVNTjA+PENPTFVNTjE+NzA3ODE0Mi4yNTwvQ09MVU1OMT48Q09MVU1OMj42MzcxNzQ8L0NPTFVNTjI+CjwvR18xPgo8R18xPgo8Q09MVU1OMD5TbWFydCBQaG9uZXM8L0NPTFVNTjA+PENPTFVNTjE+Njc3MzEyMC4zNjwvQ09MVU1OMT48Q09MVU1OMj42MzMyMTE8L0NPTFVNTjI+CjwvR18xPgo8L0RBVEFfRFM+</reportBytes><reportContentType>text/xml</reportContentType><reportFileID xsi:nil=”true”/><reportLocale xsi:nil=”true”/></runReportReturn></runReportResponse></soapenv:Body></soapenv:Envelope>

An example of the XPATH expression to retrieve just the value of reportBytes is below:


Putting these together, an example APEX statement is below:

f_report_bytes := apex_web_service.parse_xml_clob( p_xml => f_xml, p_xpath => ‘//*:reportBytes/text()’ );

Decode the Report Bytes Returned

This step uses the APEX_WEB_SERVICE package to decode the Base64 result from above into a BLOB variable and then uses the XMLTYPE function to convert the BLOB into a XMLTYPE variable.

Decoding of the Base64 result should first be tested with a Base64 decoding tool e.g. base64decode.org

An example of the APEX decode command is below:

f_blob := apex_web_service.clobbase642blob(f_base64_clob);

 An example of the XMLTYPE function is below:

f_xml := xmltype (f_blob, 1);

The decoded XML output looks like this:

<?xml version=”1.0″ encoding=”UTF-8″?>
<!–Generated by Oracle BI Publisher -Dataengine, datamodel:_custom_BIP_DEMO_MODEL_xdm –>
<COLUMN0>Cell Phones</COLUMN0><COLUMN1>6327819.47</COLUMN1><COLUMN2>478975</COLUMN2>
<COLUMN0>Smart Phones</COLUMN0><COLUMN1>6773120.36</COLUMN1><COLUMN2>633211</COLUMN2>

Create a BICS Table

This step uses a SQL command to create a simple staging table that has 20 identical varchar2 columns. These columns may be transformed into number and date data types in a future transformation exercise that is not covered in this post.

A When Others exception block allows the procedure to proceed if an error occurs because the table already exists.

A shortened example of the create table statement is below:

execute immediate ‘create table staging_table ( c01 varchar2(2048), … , c20 varchar2(2048)  )’;

Load the BICS Table

This step uses SQL commands to truncate the staging table and insert rows from the BIP report XML content.

The XML content is parsed using an XPATH command inside two LOOP commands.

The first loop processes the rows by incrementing a subscript.  It exits when the first column of a new row returns a null value.  The second loop processes the columns within a row by incrementing a subscript. It exits when a column within the row returns a null value.

The following XPATH examples are for a data set that contains 11 rows and 3 columns per row:

//G_1[2]/*[1]text()          — Returns the value of the first column of the second row

//G_1[2]/*[4]text()          — Returns a null value for the 4th column signaling the end of the row

//G_1[12]/*[1]text()        — Returns a null value for the first column of a new row signaling the end of the — data set

After each row is parsed, it is inserted into the BICS staging table.

An image of the staging table result is shown below:

BIP Table Output



This post detailed a method of loading data that has been extracted from Oracle Business Intelligence Publisher (BIP) into the Oracle Business Intelligence Cloud Service (BICS).

Data was extracted and parsed from an XML-formatted BIP report using SOAP web services wrapped in the Oracle PL/SQL APEX_WEB_SERVICE package.

A BICS staging table was created and populated. This table can then be transformed into star-schema objects for use in modeling.

For more BICS and BI best practices, tips, tricks, and guidance that the A-Team members gain from real-world experiences working with customers and partners, visit Oracle A-Team Chronicles for BICS.


Complete Text of Procedure Described

Using Oracle BI Publisher to Extract Data from Oracle Sales and ERP Clouds

Database PL/SQL Language Reference

Reference Guide for the APEX_WEB_SERVICE

Soap API Testing Tool

XPATH Testing Tool

Base64 Decoding and Encoding Testing Tool

Add Your Comment