Background

* This blog was last tested on Oracle Service Cloud 20B (Build 266) + ADW 18c + CXDev Toolbox 19.10.4.0 *

This article describes how to integrate Oracle Service Cloud (RightNow) with PL/SQL, illustrating how to programmatically load Service Cloud data into Oracle Autonomous Data Warehouse (ADW), making it readily available to model and display in Oracle Analytics Cloud (OAC).

PLEASE NOTE – this article is intended to provide the reader with some ideas of how to start an integration with Service Cloud.  The reader would also need to consider other factors such as Limits, Fault Tolerance, Scalability, Efficiency, Latency, etc which are common with any enterprise integration.  This article is intended to be part of that overall integration, and not the only piece.  Please consult with an integration expert.

Two artifacts are created in the process:

1)    ADW Database Table

The table is created in ADW to load / store the Service Cloud data.

2)    Stored Procedure

A stored procedure is created in ADW. The stored procedure uses the apex_web_service.make_request function to call an external SOAP web service; that runs a ROQL query returning the results from Service Cloud in XML format. The apex_web_service.parse_xml function is used to parse the XML output to a delimited list. The results are then separated into columns and inserted into the database table created in step 1.

The seven steps below are covered in detail to assist in replicating and testing the integration solution. Those already very familiar with “Connect Webservices for SOAP” and “ROQL”, may prefer to jump straight to step five. That said, steps one to four are very useful for constructing, comprehending and debugging the apex_web_service.make_request and apex_web_service.parse_xml functions referenced in the stored procedure.

1)    Test Standard WSDL

2)    Construct ROQL Query

3)    Build Soap Envelope

4)    Formulate XPath

5)    Create Database Table

6)    Create Stored Procedure

7)    Execute Stored Procedure

 Test Standard WSDL

1.1 – Test the Standard WSDL

https://<your_site>/services/soap/connect/soap?wsdl=typed

 Construct ROQL Query

CXDev Toolbox can be used to build and test ROQL.

It can be downloaded from: http://cxdevtoolbox.com/

2.1 – Create a new connection

Site: https://<your_site>

URL: https://<your_site>/services/soap/connect/soap

2.2 – Click ROQL Tester

2.3 – Select Query Type = CSV

Select * to exaime field names

2.4 – Type in the ROQL

For Example:

Select ID, Name.First,Contact.Address.City
From Contact
Where ID Between 1 and 100;

2.7 – Click Run Query. Results will be returned in CSV format. Ensure the query returns results and runs error free before continuing.

 Build Soap Envelope

3.1 – Use SoapUI http://www.soapui.org to test the envelope.

Create a New SOAP Project. Give it any name. The Initial WSDL is what was tested in the previous step (remember to add wsdl-typed at the end).

URL: https://<your_site>/services/soap/connect/soap?wsdl=typed

3.2 – Double Click on QueryCSV -> Request

3.3 – Delete everything from the request and replace with the following.

Replace Username and Password with the Oracle Service Cloud Username / Password.

Replace the SQL if the ROQL Query has been customized.

<soapenv:Envelope xmlns:soapenv=”http://schemas.xmlsoap.org/soap/envelope/”>
    <soapenv:Header>
        <ns7:ClientInfoHeader xmlns:ns7=”urn:messages.ws.rightnow.com/v1_4″ soapenv:mustUnderstand=”0″>
            <ns7:AppID>Basic Query CSV</ns7:AppID>
        </ns7:ClientInfoHeader>
        <wsse:Security xmlns:wsse=”http://docs.oasis-open.org/wss/2004/01/oasis-200401-wss-wssecurity-secext-1.0.xsd” mustUnderstand=”1″>
            <wsse:UsernameToken>
                <wsse:Username>Username</wsse:Username>
                <wsse:Password Type=”http://docs.oasis-open.org/wss/2004/01/oasis-200401-wss-username-token-profile-1.0#PasswordText”>Password</wsse:Password>
            </wsse:UsernameToken>
        </wsse:Security>
    </soapenv:Header>
    <soapenv:Body>
        <ns7:QueryCSV xmlns:ns7=”urn:messages.ws.rightnow.com/v1_4″>
            <ns7:Query>Select ID, Name.First,Contact.Address.City from Contact where ID between 1 and 100</ns7:Query>
            <ns7:PageSize>10000</ns7:PageSize>
        </ns7:QueryCSV>
    </soapenv:Body>
</soapenv:Envelope>  

3.4 – Submit the request. It should return the ID, First Name and City details in XML. For example

 Formulate XPath

4.1 – Free Formatter XPath Tester can be used to build and test the XPath. This is particularly helpful for debugging.

http://www.freeformatter.com/xpath-tester.html

Copy the XML output returned by SOAPUI into the XML Input box.

<soapenv:Envelope xmlns:soapenv=”http://schemas.xmlsoap.org/soap/envelope/”>
   <soapenv:Header>
      <n0:APIAccessResponseHeader xmlns:n0=”urn:messages.ws.rightnow.com/v1_4″ xmlns:xsi=”http://www.w3.org/2001/XMLSchema-instance”>
         <n0:Token>abc=</n0:Token>
         <n0:NextRequestAfter>0</n0:NextRequestAfter>
      </n0:APIAccessResponseHeader>
   </soapenv:Header>
   <soapenv:Body>
      <n0:QueryCSVResponse xmlns:n0=”urn:messages.ws.rightnow.com/v1_4″ xmlns:xsi=”http://www.w3.org/2001/XMLSchema-instance”>
         <n0:CSVTableSet>
            <n0:CSVTables>
               <n0:CSVTable>
                  <n0:Name>Contact</n0:Name>
                  <n0:Columns>ID,First,City</n0:Columns>
                  <n0:Rows>
                     <n0:Row>1,Jesse,San Francisco</n0:Row>
                     <n0:Row>2,Randy,</n0:Row>
                     <n0:Row>4,Chris,Bozeman</n0:Row>
                  </n0:Rows>
               </n0:CSVTable>
            </n0:CSVTables>
         </n0:CSVTableSet>
      </n0:QueryCSVResponse>
   </soapenv:Body>
</soapenv:Envelope>

4.2 – Copy the following XPath in the XPath expression box.

//n0:QueryCSVResponse/n0:CSVTableSet/n0:CSVTables/n0:CSVTable/n0:Rows/n0:Row/text()

4.3 – Click: TEST XPATH

4.4 – Results

The results will be returned in a delimited list. If an error has occurred, the XPath may need correcting.

 Create Database Table

5.1 – Create table in ADW using SQLDeveloper or SQL query tool of choice.

CREATE TABLE OAC_USER.SERVICE_CLOUD
(“ID” NUMBER,
“FIRST_NAME” VARCHAR2(100 BYTE),
“CITY” VARCHAR2(100 BYTE)
);

 Create Stored Procedure

Create and run the stored procedure that calls the web service to pull the Service Cloud data and load’s the data into the SERVICE_CLOUD table.

The stored procedure leverages the APEX_WEB_SERVICE API (using Apex_web_service.make_request). This example invokes a SOAP style web service. However, the APEX_WEB_SERVICE API can also be used to invoke RESTful style web services. The web service returns the results in XML. Apex_web_service.parse_xml is used to extract the data set. It is returned as a delimiter list – separated by “~”. The results are then extracted into columns and then loaded into the database table.

Replace the following highlighted values:

1)    l_envelope – If changed in “Step Three – Build the Soap Envelope” then replace with custom envelope.

2)    Username

3)    Password

4)    ROQL Query – The ROQL Query constructed in “Step Two – Construct the ROQL Query“.

5)    p_url – The Standard WSDL obtained in “Step One – Obtain the Standard WSDL” excluding the endpoint. Should end in /soap.

6)    p_xpath – If changed it in “Step Four – Formulate the XPath” then replace with custom Xpath.

7)    Table Name –  The name of the table created in “Step Five – Create Database Table“

8)    Insert Values – Duplicate for each column. Increase by +1 in the second regexp_substr parameter / position.

CREATE OR REPLACE PROCEDURE OAC_USER.RN_INTEGRATION
IS
l_envelope CLOB;
l_xml XMLTYPE;
f_index number ;
l_str varchar2(1000);
BEGIN
l_envelope := ‘<soapenv:Envelope xmlns:soapenv=”http://schemas.xmlsoap.org/soap/envelope/”>
    <soapenv:Header>
        <ns7:ClientInfoHeader xmlns:ns7=”urn:messages.ws.rightnow.com/v1_4″ soapenv:mustUnderstand=”0″>
            <ns7:AppID>Basic Query CSV</ns7:AppID>
        </ns7:ClientInfoHeader>
        <wsse:Security xmlns:wsse=”http://docs.oasis-open.org/wss/2004/01/oasis-200401-wss-wssecurity-secext-1.0.xsd” mustUnderstand=”1″>
            <wsse:UsernameToken>
                <wsse:Username>User</wsse:Username>
                <wsse:Password Type=”http://docs.oasis-open.org/wss/2004/01/oasis-200401-wss-username-token-profile-1.0#PasswordText”>Password</wsse:Password>
            </wsse:UsernameToken>
        </wsse:Security>
    </soapenv:Header>
    <soapenv:Body>
        <ns7:QueryCSV xmlns:ns7=”urn:messages.ws.rightnow.com/v1_4″>
            <ns7:Query>Select ID, Name.First,Contact.Address.City from Contact where ID between 1 and 100</ns7:Query>
            <ns7:PageSize>10000</ns7:PageSize>
        </ns7:QueryCSV>
    </soapenv:Body>
</soapenv:Envelope>’;
l_xml := apex_web_service.make_request(
p_url => ‘https://<your_cfg_site>/services/soap/connect/soap’
,p_envelope => l_envelope
–,p_proxy_override => ‘www-proxy.us.oracle.com’
);
–dbms_output.put_line ( l_xml.getCLOBVal() );
DELETE FROM OAC_USER.SERVICE_CLOUD;
f_index := 0;
LOOP
f_index := f_index +1;
l_str := apex_web_service.parse_xml(
p_xml => l_xml,
p_xpath => ‘//n0:QueryCSVResponse/n0:CSVTableSet/n0:CSVTables/n0:CSVTable/n0:Rows/n0:Row[‘||to_char(f_index)||’]/text()‘,
p_ns => ‘xmlns:n0=”urn:messages.ws.rightnow.com/v1_4″‘ );
— dbms_output.put_line(dbms_lob.substr(l_str,24000,1));
INSERT INTO OAC_USER.SERVICE_CLOUD
values (
regexp_substr(l_str, ‘[^,]+’, 1, 1),
regexp_substr(l_str, ‘[^,]+’, 1, 2),
regexp_substr(l_str, ‘[^,]+’, 1, 3)
);
exit WHEN l_str is null;
end loop;
commit;
END;
/

 Execute Stored Procedure

7.1 – Execute the Stored Procedure in ADW

begin
OAC_USER.RN_INTEGRATION();
end;
/

7.2 – Confirm data loaded correctly.

SELECT * FROM OAC_USER.SERVICE_CLOUD;

 Want to Learn More?

Click here for more A-Team OAC Blogs

Click here for more A-Team BICS Blogs

Click here for more A-Team OBIEE Blogs

Click here for the Application Express API Reference Guide – Chapter 22 – APEX_WEB_SERVICE.

Click here for the Oracle Service Cloud Connect Web Services for SOAP Developers Guide.

Click here for the Oracle Service Cloud Connect REST API Developers Guide.

 Summary

This article has provided a set of example artifacts that leverage the APEX_WEB_SERVICE_API to integrate Oracle Service Cloud (RightNow) with PL/SQL using Connect Web Services for SOAP. This article may also be a good starting point for those wanting to perform similar integrations using RESTful style web services. This method could also be used to integrate Oracle Autonomous Data Warehouse (ADW) or Oracle Analytics Cloud (OAC) with other applications.